1 /* **************************************************************
2 Copyright (C) 2010, 2011, 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 * \file
20 * \brief Database related operations
21 */
22 /* local includes */
23 #include <agent.h>
24 #include <database.h>
25 #include <logging.h>
26 #include <emailformatter.h>
27
28 /* other library includes */
29 #include <libfossdb.h>
30 #include <fcntl.h>
31 #include <sys/stat.h>
32 #include <sys/mman.h>
33
34 /* all of the sql statements used in the database */
35 #include <sqlstatements.h>
36
37 /* ************************************************************************** */
38 /* *** email notification *** */
39 /* *** *** */
40 /* *** There is no good location to put the code that performs the *** */
41 /* *** email notification. This is because it is one of the more *** */
42 /* *** database intensive actions that the scheduler performs, but *** */
43 /* *** also requires extensive network access. Since the database *** */
44 /* *** access is limited to database.c, this is where email *** */
45 /* *** notification lives. *** */
46 /* ************************************************************************** */
47
48 #define EMAIL_ERROR(...) { \
49 WARNING(__VA_ARGS__); \
50 email_notify = 0; \
51 error = NULL; }
52
53 #define EMAIL_BUILD_CMD "%s %s -s '%s' %s" ///< Email command format
54 #define DEFAULT_HEADER "FOSSology scan complete\nmessage:\"" ///< Default email header
55 #define DEFAULT_FOOTER "\"" ///< Default email footer
56 #define DEFAULT_SUBJECT "FOSSology scan complete\n" ///< Default email subject
57 #define DEFAULT_COMMAND "/usr/bin/mailx" ///< Default email command to use
58
59 #define min(x, y) (x < y ? x : y) ///< Return the minimum of x, y
60
61 /**
62 * We need to pass both a job_t* and the fossology url string to the
63 * email_replace() function. This structure allows both of these to be passed.
64 */
65 typedef struct {
66 scheduler_t* scheduler; ///< Current scheduler reference
67 gchar* foss_url; ///< Fossology URL string
68 job_t* job; ///< Current job structure
69 } email_replace_args;
70
71 /**
72 * @brief Replaces the variables that are in the header and footer files.
73 *
74 * This is a
75 * callback function that is passed to the glib function g_regex_replace_eval().
76 * This reads what was matched by the regex and then appends the correct
77 * information onto the GString that is passed to the function.
78 *
79 * Variables:\n
80 * $UPLOADNAME\n
81 * $BROWSELINK\n
82 * $SCHEDULERLOG\n
83 * $UPLOADFOLDERNAME\n
84 * $JOBRESULT\n
85 * $JOBQUEUELINK\n
86 * $AGENTSTATUS\n
87 * $DB.table.column [not implemented]
88 *
89 * @param match the regex match that glib found
90 * @param ret the GString* that results should be appended to.
91 * @param args The email replace arguments with foss url and job reference
92 * @return always FALSE so that g_regex_replace_eval() will continue
93 * @todo needs implementation of $DB.table.column
94 */
email_replace(const GMatchInfo * match,GString * ret,email_replace_args * args)95 static gboolean email_replace(const GMatchInfo* match, GString* ret,
96 email_replace_args* args)
97 {
98 gchar* m_str = g_match_info_fetch(match, 1);
99 gchar* sql = NULL;
100 gchar* fossy_url = args->foss_url;
101 job_t* job = args->job;
102 GPtrArray* rows = NULL;
103 /* TODO belongs to $DB if statement => gchar* table, * column; */
104 PGresult* db_result;
105 guint i;
106
107 /* $UPLOADNAME
108 *
109 * Appends the name of the file that was uploaded and appends it to the output
110 * string. This uses the job id to find the upload name.
111 */
112 if(strcmp(m_str, "UPLOADNAME") == 0)
113 {
114 sql = g_strdup_printf(upload_name, job->id);
115 db_result = database_exec(args->scheduler, sql);
116
117 if(PQresultStatus(db_result) != PGRES_TUPLES_OK)
118 {
119 g_string_append_printf(ret,
120 "[ERROR: unable to select upload file name for job %d]", job->id);
121 }
122 else if(PQntuples(db_result) == 0)
123 {
124 if(strcmp(job->agent_type, "delagent") == 0)
125 g_string_append_printf(ret,
126 "[File has been deleted by job %d]", job->id);
127 else
128 g_string_append_printf(ret,
129 "[ERROR: file has not been uploaded or unpacked yet for job %d]", job->id);
130 }
131 else
132 {
133 g_string_append(ret, PQgetvalue(db_result, 0, 0));
134 }
135
136 SafePQclear(db_result);
137 g_free(sql);
138 }
139
140 /* $BROWSELINK
141 *
142 * Appends the URL that will link to the upload in the browse menu of the user
143 * interface.
144 */
145 else if(strcmp(m_str, "BROWSELINK") == 0)
146 {
147 sql = g_strdup_printf(upload_pk, job->id);
148 db_result = database_exec(args->scheduler, sql);
149
150 if(PQresultStatus(db_result) != PGRES_TUPLES_OK)
151 {
152 g_string_append_printf(ret,
153 "[ERROR: unable to select upload primary key for job %d]", job->id);
154 }
155 else if(PQntuples(db_result) == 0)
156 {
157 if(strcmp(job->agent_type, "delagent") == 0)
158 g_string_append_printf(ret,
159 "[File has been deleted by job %d]", job->id);
160 else
161 g_string_append_printf(ret,
162 "[ERROR: file has not been uploaded or unpacked yet for job %d]", job->id);
163 }
164 else
165 {
166 g_string_append_printf(ret,
167 "http://%s?mod=browse&upload=%s&item=%s&show=detail",
168 fossy_url, PQgetvalue(db_result, 0, 0), PQgetvalue(db_result, 0, 1));
169 }
170
171 SafePQclear(db_result);
172 g_free(sql);
173 }
174
175 /* $JOBQUEUELINK
176 *
177 * Appends the URL that will link to the job queue
178 */
179 else if(strcmp(m_str, "JOBQUEUELINK") == 0)
180 {
181 sql = g_strdup_printf(upload_pk, job->id);
182 db_result = database_exec(args->scheduler, sql);
183
184 if(PQresultStatus(db_result) != PGRES_TUPLES_OK)
185 {
186 g_string_append_printf(ret,
187 "[ERROR: unable to select file name for upload %d]", job->id);
188 }
189 else
190 {
191 g_string_append_printf(ret, "http://%s?mod=showjobs&upload=%s",
192 fossy_url, PQgetvalue(db_result, 0, 0));
193 }
194
195 SafePQclear(db_result);
196 g_free(sql);
197 }
198
199 /* $SCHEDULERLOG
200 *
201 * Appends the URL that will link to the log file produced by the agent.
202 */
203 else if(strcmp(m_str, "SCHEDULERLOG") == 0)
204 {
205 g_string_append_printf(ret, "http://%s?mod=showjobs&job=%d",
206 fossy_url, job->id);
207 }
208
209 /* $UPLOADFOLDERNAME
210 *
211 * Appends the name of the folder that the upload was stored under.
212 */
213 else if(strcmp(m_str, "UPLOADFOLDERNAME") == 0)
214 {
215 sql = g_strdup_printf(folder_name, job->id);
216 db_result = database_exec(args->scheduler, sql);
217
218 if(PQresultStatus(db_result) != PGRES_TUPLES_OK || PQntuples(db_result) == 0)
219 {
220 g_string_append_printf(ret,
221 "[NOTICE: unable to select folder name for upload %d]", job->id);
222 }
223 else
224 {
225 rows = g_ptr_array_new();
226 GString *foldername = g_string_new(PQgetvalue(db_result, 0, 0));
227 guint folder_pk = atoi(PQget(db_result, 0, "folder_pk"));
228 g_ptr_array_add(rows, foldername);
229 SafePQclear(db_result);
230 g_free(sql);
231 sql = g_strdup_printf(parent_folder_name, folder_pk);
232 db_result = database_exec(args->scheduler, sql);
233 /*
234 * Get the current folder name and traverse back till the root folder.
235 * Add the folder names found in an array.
236 * array[0] => Curr_Folder
237 * array[1] => Par_Folder
238 * array[2] => Root_Folder
239 */
240 while(PQresultStatus(db_result) == PGRES_TUPLES_OK && PQntuples(db_result) == 1)
241 {
242 GString *foldername = g_string_new(PQgetvalue(db_result, 0, 0));
243 guint folder_pk = atoi(PQget(db_result, 0, "folder_pk"));
244 g_ptr_array_add(rows, foldername);
245 SafePQclear(db_result);
246 g_free(sql);
247 sql = g_strdup_printf(parent_folder_name, folder_pk);
248 db_result = database_exec(args->scheduler, sql);
249 }
250 /*
251 * Traverse the folder name array from behind and append the names with a
252 * `/` as a separator between the names.
253 * Result => Root_Folder / Par_Folder / Curr_folder
254 */
255 for(i = rows->len - 1; i > 0; i--)
256 {
257 GString *folder = g_ptr_array_index(rows, i);
258 g_string_append(ret, folder->str);
259 g_string_append(ret, " / ");
260 }
261 GString *folder = g_ptr_array_index(rows, 0);
262 g_string_append(ret, folder->str);
263 g_ptr_array_free(rows, TRUE);
264 }
265
266 SafePQclear(db_result);
267 g_free(sql);
268 }
269
270 /* $JOBRESULT
271 *
272 * Appends if the job finished successfully or it it failed.
273 */
274 else if(strcmp(m_str, "JOBRESULT") == 0)
275 {
276 switch(job->status)
277 {
278 case JB_COMPLETE: g_string_append(ret, "COMPLETE"); break;
279 case JB_FAILED: g_string_append(ret, "FAILED"); break;
280 default:
281 g_string_append_printf(ret, "[ERROR: illegal job status \"%s\"]",
282 job_status_strings[job->status]);
283 break;
284 }
285 }
286
287 /* $AGENTSTATUS
288 *
289 * Appends the list of agents run with their status.
290 */
291 else if(strcmp(m_str, "AGENTSTATUS") == 0)
292 {
293 sql = g_strdup_printf(jobsql_jobinfo, job->id);
294 db_result = database_exec(args->scheduler, sql);
295 if(PQresultStatus(db_result) != PGRES_TUPLES_OK)
296 {
297 g_string_append_printf(ret,
298 "[ERROR: unable to select agent info for job %d]", job->id);
299 }
300 else
301 {
302 rows = g_ptr_array_sized_new(PQntuples(db_result));
303 /*
304 * Find all the agents for the current job and attach their jq_pk,
305 * their name and their status (true=>pass, false=>fail)
306 */
307 for(i = 0; i < PQntuples(db_result) && ret; i++)
308 {
309 agent_info *data = (agent_info *)calloc(1, sizeof(agent_info));
310 data->id = atoi(PQget(db_result, i, "jq_pk"));
311 data->agent = g_string_new(PQget(db_result, i, "jq_type"));
312 if(atoi(PQget(db_result, i, "jq_end_bits")) == 1)
313 {
314 data->status = TRUE;
315 }
316 else
317 {
318 data->status = FALSE;
319 }
320 g_ptr_array_add(rows, data);
321 }
322 /* Pass the agent data to email_formating function to convert in desired format */
323 g_string_append(ret, email_format_text(rows, fossy_url));
324 g_ptr_array_free(rows, TRUE);
325 }
326 SafePQclear(db_result);
327 g_free(sql);
328 }
329
330 /* $DB.table.column
331 *
332 * Appends a column of a table from the database to the resulting string.
333 */
334 else if(strcmp(m_str, "DB") == 0)
335 {
336 g_string_append(ret, "[DB. syntax is NOT IMPLEMENTED]");
337 /* TODO reimplement $DB variable
338 table = g_match_info_fetch(match, 3);
339 column = g_match_info_fetch(match, 4);
340 sql = g_strdup_printf("SELECT %s FROM %s;", column, table);
341 db_result = database_exec(scheduler, sql);
342 if(PQresultStatus(db_result) != PGRES_TUPLES_OK ||
343 PQntuples(db_result) == 0 || PQnfields(db_result) == 0)
344 {
345 g_string_append_printf(ret, "[ERROR: unable to select %s.%s]", table, column);
346 }
347 else
348 {
349 g_string_append_printf(ret, "%s.%s[", table, column);
350 for(i = 0; i < PQntuples(db_result); i++)
351 {
352 g_string_append(ret, PQgetvalue(db_result, i, 0));
353 if(i != PQntuples(db_result) - 1)
354 g_string_append(ret, " ");
355 }
356 g_string_append(ret, "]");
357 }
358
359 SafePQclear(db_result);
360 g_free(sql);
361 g_free(table);
362 g_free(column);*/
363 }
364
365 g_free(m_str);
366 return FALSE;
367 }
368
369 /**
370 * @brief Checks the database for the status of the job
371 *
372 * @param scheduler Current scheduler reference
373 * @param job The job to check for the job status on
374 * @return 0: job is not finished, 1: job has finished, 2: job has failed
375 */
email_checkjobstatus(scheduler_t * scheduler,job_t * job)376 static gint email_checkjobstatus(scheduler_t* scheduler, job_t* job)
377 {
378 gchar* sql;
379 gint ret = 1;
380 PGresult* db_result;
381 int id, i;
382
383 sql = g_strdup_printf(jobsql_anyrunnable, job->id);
384 db_result = database_exec(scheduler, sql);
385 if(PQresultStatus(db_result) != PGRES_TUPLES_OK)
386 {
387 PQ_ERROR(db_result, "unable to check job status for jq_pk %d", job->id);
388 g_free(sql);
389 SafePQclear(db_result);
390 return 0;
391 }
392
393 /* check if all runnable jobs have been run */
394 if(PQntuples(db_result) != 0)
395 {
396 ret = 0;
397 }
398
399 g_free(sql);
400 SafePQclear(db_result);
401
402 sql = g_strdup_printf(jobsql_jobendbits, job->id);
403 db_result = database_exec(scheduler, sql);
404
405 /* check for any jobs that are still running */
406 for(i = 0; i < PQntuples(db_result) && ret; i++)
407 {
408 id = atoi(PQget(db_result, i, "jq_pk"));
409 if(id != job->id && g_tree_lookup(scheduler->job_list, &id) != NULL)
410 {
411 ret = 0;
412 break;
413 }
414 }
415
416 /* check for any failed jobs */
417 for(i = 0; i < PQntuples(db_result) && ret; i++)
418 {
419 if(atoi(PQget(db_result, i, "jq_end_bits")) == (1 << 1))
420 {
421 ret = 2;
422 break;
423 }
424 }
425
426 g_free(sql);
427 SafePQclear(db_result);
428 return ret;
429 }
430
431 /**
432 * Sends an email notification that a particular job has completed correctly.
433 * This compiles the email based upon the header file, footer file, and the job
434 * that just completed.
435 *
436 * @param scheduler Current scheduler reference
437 * @param job The job that just finished
438 * @return void, no return
439 */
email_notification(scheduler_t * scheduler,job_t * job)440 static void email_notification(scheduler_t* scheduler, job_t* job)
441 {
442 PGresult* db_result;
443 int j_id = job->id;
444 int upload_id;
445 int status;
446 int retcode;
447 char* val;
448 char* final_cmd = NULL;
449 char sql[1024];
450 FILE* mail_io;
451 GString* email_txt;
452 job_status curr_status = job->status;
453 email_replace_args args;
454
455 if(is_meta_special(g_tree_lookup(scheduler->meta_agents, job->agent_type), SAG_NOEMAIL) ||
456 !(status = email_checkjobstatus(scheduler, job)))
457 return;
458
459 sprintf(sql, select_upload_fk, j_id);
460 db_result = database_exec(scheduler, sql);
461 if(PQresultStatus(db_result) != PGRES_TUPLES_OK || PQntuples(db_result) == 0)
462 {
463 PQ_ERROR(db_result, "unable to select the upload id for job %d", j_id);
464 return;
465 }
466
467 upload_id = atoi(PQgetvalue(db_result, 0, 0));
468 SafePQclear(db_result);
469
470 sprintf(sql, upload_common, upload_id);
471 db_result = database_exec(scheduler, sql);
472 if(PQresultStatus(db_result) != PGRES_TUPLES_OK)
473 {
474 PQ_ERROR(db_result, "unable to check common uploads to job %d", j_id);
475 return;
476 }
477 SafePQclear(db_result);
478
479 sprintf(sql, jobsql_email, upload_id);
480 db_result = database_exec(scheduler, sql);
481 if(PQresultStatus(db_result) != PGRES_TUPLES_OK)
482 {
483 PQ_ERROR(db_result, "unable to access email info for job %d", j_id);
484 return;
485 }
486
487 /* special for delagent, upload records have been deleted.
488 * So can't get the user info from upload table.
489 * So get the user info from job table */
490 if(PQntuples(db_result) == 0)
491 {
492 SafePQclear(db_result);
493 sprintf(sql, jobsql_email_job, j_id);
494 db_result = database_exec(scheduler, sql);
495 if(PQresultStatus(db_result) != PGRES_TUPLES_OK || PQntuples(db_result) == 0)
496 {
497 PQ_ERROR(db_result, "unable to access email info for job %d", j_id);
498 return;
499 }
500 }
501
502 if(PQget(db_result, 0, "email_notify")[0] == 'y')
503 {
504 if(status == 2)
505 job->status = JB_FAILED;
506
507 email_txt = g_string_new("");
508 g_string_append(email_txt, scheduler->email_header);
509 g_string_append(email_txt, job->message == NULL ? "" : job->message);
510 g_string_append(email_txt, scheduler->email_footer);
511
512
513 if(scheduler->parse_db_email != NULL)
514 {
515 args.foss_url = scheduler->host_url;
516 args.job = job;
517 args.scheduler = scheduler;
518 val = g_regex_replace_eval(scheduler->parse_db_email, email_txt->str,
519 email_txt->len, 0, 0, (GRegexEvalCallback)email_replace, &args, NULL);
520 }
521 else
522 {
523 val = email_txt->str;
524 }
525
526 final_cmd = get_email_command(scheduler, PQget(db_result, 0, "user_email"));
527 if(final_cmd == NULL)
528 {
529 if(scheduler->parse_db_email != NULL)
530 g_free(val);
531 g_string_free(email_txt, TRUE);
532 return;
533 }
534 if((mail_io = popen(final_cmd, "w")) != NULL)
535 {
536 fprintf(mail_io, "%s", val);
537 fflush(mail_io);
538 retcode = WEXITSTATUS(pclose(mail_io));
539 if(retcode != 0)
540 {
541 ERROR("Received error code %d from '%s'", retcode, scheduler->email_command);
542 }
543 }
544 else
545 {
546 WARNING("Unable to spawn email notification process: '%s'.\n",
547 scheduler->email_command);
548 }
549 job->status = curr_status;
550 if(scheduler->parse_db_email != NULL)
551 g_free(val);
552 g_free(final_cmd);
553 g_string_free(email_txt, TRUE);
554 }
555 SafePQclear(db_result);
556 }
557
558 /**
559 * @brief Loads information about the email that will be sent for job notifications.
560 *
561 * This loads the header and footer configuration files, loads the subject and
562 * client info, and compiles the regex that is used to replace variables in the
563 * header and footer files.
564 *
565 * @param[in,out] scheduler Current scheduler to init
566 * @return void, no return
567 */
email_init(scheduler_t * scheduler)568 void email_init(scheduler_t* scheduler)
569 {
570 int email_notify, fd;
571 struct stat header_sb = {};
572 struct stat footer_sb = {};
573 gchar* fname;
574 GError* error = NULL;
575
576 if(scheduler->email_header && !scheduler->default_header)
577 munmap(scheduler->email_header, header_sb.st_size);
578 if(scheduler->email_footer && !scheduler->default_footer)
579 munmap(scheduler->email_footer, footer_sb.st_size);
580 g_free(scheduler->email_subject);
581
582 /* load the header */
583 email_notify = 1;
584 fname = g_strdup_printf("%s/%s", scheduler->sysconfigdir,
585 fo_config_get(scheduler->sysconfig, "EMAILNOTIFY", "header", &error));
586 if(error && error->code == fo_missing_group)
587 EMAIL_ERROR("email notification setting group \"[EMAILNOTIFY]\" missing. Using defaults");
588 if(error && error->code == fo_missing_key)
589 EMAIL_ERROR("email notification setting key \"header\" missing. Using default header");
590 if(email_notify && (fd = open(fname, O_RDONLY)) == -1)
591 EMAIL_ERROR("unable to open file for email header: %s", fname);
592 if(email_notify && fstat(fd, &header_sb) == -1)
593 EMAIL_ERROR("unable to fstat email header: %s", fname);
594 if(email_notify && (scheduler->email_header = mmap(NULL, header_sb.st_size, PROT_READ,
595 MAP_SHARED, fd, 0)) == MAP_FAILED)
596 EMAIL_ERROR("unable to mmap email header: %s", fname);
597 if((scheduler->default_header = !email_notify))
598 scheduler->email_header = DEFAULT_HEADER;
599
600 /* load the footer */
601 email_notify = 1;
602 fname = g_strdup_printf("%s/%s", scheduler->sysconfigdir,
603 fo_config_get(scheduler->sysconfig, "EMAILNOTIFY", "footer", &error));
604 if(error)
605 email_notify = 0;
606 if(error && error->code == fo_missing_key)
607 EMAIL_ERROR("email notification setting key \"footer\" missing. Using default footer");
608 if(email_notify && (fd = open(fname, O_RDONLY)) == -1)
609 EMAIL_ERROR("unable to open file for email footer: %s", fname);
610 if(email_notify && fstat(fd, &footer_sb) == -1)
611 EMAIL_ERROR("unable to fstat email footer: %s", fname);
612 if(email_notify && (scheduler->email_footer = mmap(NULL, footer_sb.st_size, PROT_READ,
613 MAP_SHARED, fd, 0)) == MAP_FAILED)
614 EMAIL_ERROR("unable to mmap email footer: %s", fname);
615 if((scheduler->default_footer = !email_notify))
616 scheduler->email_footer = DEFAULT_FOOTER;
617 error = NULL;
618
619 /* load the email_subject */
620 scheduler->email_subject = fo_config_get(scheduler->sysconfig, "EMAILNOTIFY",
621 "subject", &error);
622 if(error)
623 scheduler->email_subject = DEFAULT_SUBJECT;
624 if(error && error->code == fo_missing_key)
625 EMAIL_ERROR("email notification setting key \"subject\" missing. Using default subject");
626 scheduler->email_subject = g_strdup(scheduler->email_subject);
627 error = NULL;
628
629 /* load the client */
630 email_notify = 1;
631 scheduler->email_command = fo_config_get(scheduler->sysconfig, "EMAILNOTIFY",
632 "client", &error);
633 if(error)
634 scheduler->email_command = DEFAULT_COMMAND;
635 if(error && error->code == fo_missing_key)
636 EMAIL_ERROR("email notification setting key \"client\" missing. Using default client");
637 scheduler->email_command = g_strdup(scheduler->email_command);
638 error = NULL;
639 }
640
641 /* ************************************************************************** */
642 /* **** local functions ***************************************************** */
643 /* ************************************************************************** */
644
645 /**
646 * @brief Data type used to check if the database is correct.
647 *
648 * This will be statically initialized in the check_tables() function.
649 */
650 typedef struct
651 {
652 char* table; ///< The name of the table to check columns in
653 uint8_t ncols; ///< The number of columns in the table that the scheduler uses
654 char* columns[13]; ///< The columns that the scheduler uses for this table
655 } reqcols;
656
657 /**
658 * @brief Checks that any part of the database used by the scheduler is correct
659 *
660 * This has a static list of all tables and the associated columns used by the
661 * scheduler. If any changes that affect the tables and columns used by the
662 * scheduler are made, this static list should be updated.
663 *
664 * @param scheduler the scheduler_t* that holds the connection
665 */
check_tables(scheduler_t * scheduler)666 static void check_tables(scheduler_t* scheduler)
667 {
668 /* locals */
669 PGresult* db_result;
670 GString* sql;
671 reqcols* curr;
672 uint32_t i;
673 uint32_t curr_row;
674 int passed = TRUE;
675 char sqltmp[1024] = {0};
676
677 /* All of the tables and columns that the scheduler uses
678 *
679 * Note: the columns should be listed in alphabetical order, if they are not
680 * then the error messages that result will be erroneous
681 */
682 reqcols cols[] =
683 {
684 {"jobqueue", 13, {
685 "jq_args", "jq_end_bits", "jq_endtext", "jq_endtime", "jq_host",
686 "jq_itemsprocessed", "jq_job_fk", "jq_log", "jq_pk", "jq_runonpfile",
687 "jq_schedinfo", "jq_starttime", "jq_type" }},
688 {"sysconfig", 2, {"conf_value", "variablename" }},
689 {"job", 2, {"job_pk", "job_upload_fk" }},
690 {"folder", 2, {"folder_name", "folder_pk" }},
691 {"foldercontents", 3, {"child_id", "foldercontents_mode", "parent_fk" }},
692 {"upload", 2, {"upload_filename", "upload_pk" }},
693 {"uploadtree", 3, {"parent", "upload_fk", "uploadtree_pk" }},
694 {"users", 4, {"email_notify", "user_email", "user_name", "user_pk" }},
695 { NULL }
696 };
697
698 /* 1st iterate across every require table and column */
699 sprintf(sqltmp, check_scheduler_tables, PQdb(scheduler->db_conn));
700 for(curr = cols; curr->table; curr++)
701 {
702 /* build the sql statement */
703 sql = g_string_new(sqltmp);
704 g_string_append_printf(sql, "'%s' AND column_name IN (", curr->table);
705 for(i = 0; i < curr->ncols; i++)
706 {
707 g_string_append_printf(sql, "'%s'", curr->columns[i]);
708 if(i != curr->ncols - 1)
709 g_string_append(sql, ", ");
710 }
711 g_string_append(sql, ") ORDER BY column_name");
712
713 /* execute the sql */
714 db_result = database_exec(scheduler, sql->str);
715 if(PQresultStatus(db_result) != PGRES_TUPLES_OK)
716 {
717 passed = FALSE;
718 PQ_ERROR(db_result, "could not check database tables");
719 break;
720 }
721
722 /* check that the correct number of columns was returned yr */
723 if(PQntuples(db_result) != curr->ncols)
724 {
725 /* we have failed the database check */
726 passed = FALSE;
727
728 /* print the columns that do not exist */
729 for(i = 0, curr_row = 0; i < curr->ncols; i++)
730 {
731 if(curr_row>=PQntuples(db_result) || strcmp(PQgetvalue(db_result, curr_row, 0), curr->columns[i]) != 0)
732 {
733 ERROR("Column %s.%s does not exist", curr->table, curr->columns[i]);
734 }
735 else
736 {
737 curr_row++;
738 }
739 }
740 }
741
742 SafePQclear(db_result);
743 g_string_free(sql, TRUE);
744 }
745
746 if(!passed)
747 {
748 log_printf("FATAL %s.%d: Scheduler did not pass database check\n", __FILE__, __LINE__);
749 log_printf("FATAL %s.%d: Running fo_postinstall should fix the database schema\n", __FILE__, __LINE__);
750 exit(230);
751 }
752 }
753
754 /**
755 * @brief Creates and performs error checking for a new database connection
756 *
757 * @param configdir the location of the Db.conf file
758 * @return a new database connection
759 */
database_connect(gchar * configdir)760 static PGconn* database_connect(gchar* configdir)
761 {
762 PGconn* ret = NULL;
763 gchar* dbconf = NULL;
764 char* error = NULL;
765
766 dbconf = g_strdup_printf("%s/Db.conf", configdir);
767 ret = fo_dbconnect(dbconf, &error);
768
769 if(error || PQstatus(ret) != CONNECTION_OK)
770 FATAL("Unable to connect to the database: \"%s\"", error);
771
772 g_free(dbconf);
773 return ret;
774 }
775
776 /**
777 * Initializes any one-time attributes relating to the database. Currently this
778 * includes creating the db connection and checking the URL of the FOSSology
779 * instance out of the db.
780 */
database_init(scheduler_t * scheduler)781 void database_init(scheduler_t* scheduler)
782 {
783 PGresult* db_result;
784
785 /* create the connection to the database */
786 scheduler->db_conn = database_connect(scheduler->sysconfigdir);
787
788 /* get the url for the fossology instance */
789 db_result = database_exec(scheduler, url_checkout);
790 if(PQresultStatus(db_result) == PGRES_TUPLES_OK && PQntuples(db_result) != 0)
791 scheduler->host_url = g_strdup(PQgetvalue(db_result, 0, 0));
792 SafePQclear(db_result);
793
794 /* check that relevant database fields exist */
795 check_tables(scheduler);
796 }
797
798 /* ************************************************************************** */
799 /* **** event and functions ************************************************* */
800 /* ************************************************************************** */
801
802 /**
803 * @brief Executes an sql statement for the scheduler
804 *
805 * This is used in case the database connection is lost. The scheduler requires
806 * a connection to the database to correctly operate. So if the connection is
807 * ever lost we automatically try to reconnect and if we are unable to, the
808 * scheduler will die.
809 *
810 * @param scheduler the scheduler_t* that holds the connection
811 * @param sql the sql that will be performed
812 * @return the PGresult struct that is returned by PQexec
813 */
database_exec(scheduler_t * scheduler,const char * sql)814 PGresult* database_exec(scheduler_t* scheduler, const char* sql)
815 {
816 PGresult* ret = NULL;
817
818 V_SPECIAL("DATABASE: exec \"%s\"\n", sql);
819
820 ret = PQexec(scheduler->db_conn, sql);
821 if(ret == NULL || PQstatus(scheduler->db_conn) != CONNECTION_OK)
822 {
823 PQfinish(scheduler->db_conn);
824 scheduler->db_conn = database_connect(scheduler->sysconfigdir);
825
826 ret = PQexec(scheduler->db_conn, sql);
827 }
828
829 return ret;
830 }
831
832 /**
833 * @todo
834 *
835 * @param scheduler
836 * @param sql
837 */
database_exec_event(scheduler_t * scheduler,char * sql)838 void database_exec_event(scheduler_t* scheduler, char* sql)
839 {
840 PGresult* db_result = database_exec(scheduler, sql);
841 if(PQresultStatus(db_result) != PGRES_COMMAND_OK)
842 PQ_ERROR(db_result, "failed to perform database exec: %s", sql);
843 g_free(sql);
844 }
845
846 /**
847 * @brief Checks the job queue for any new entries.
848 *
849 * @param scheduler The scheduler_t* that holds the connection
850 * @param unused
851 */
database_update_event(scheduler_t * scheduler,void * unused)852 void database_update_event(scheduler_t* scheduler, void* unused)
853 {
854 /* locals */
855 PGresult* db_result;
856 PGresult* pri_result;
857 int i, j_id;
858 char sql[512];
859 char* value, * type, * host, * pfile, * parent, *jq_cmd_args;
860 job_t* job;
861
862 if(closing)
863 {
864 WARNING("scheduler is closing, will not check the job queue");
865 return;
866 }
867
868 /* make the database query */
869 db_result = database_exec(scheduler, basic_checkout);
870 if(PQresultStatus(db_result) != PGRES_TUPLES_OK)
871 {
872 PQ_ERROR(db_result, "database update failed on call to PQexec");
873 return;
874 }
875
876 V_SPECIAL("DB: retrieved %d entries from the job queue\n",
877 PQntuples(db_result));
878 for(i = 0; i < PQntuples(db_result); i++)
879 {
880 /* start by checking that the job hasn't already been grabbed */
881 j_id = atoi(PQget(db_result, i, "jq_pk"));
882 if(g_tree_lookup(scheduler->job_list, &j_id) != NULL)
883 continue;
884
885 /* get relevant values out of the job queue */
886 parent = PQget(db_result, i, "jq_job_fk");
887 host = PQget(db_result, i, "jq_host");
888 type = PQget(db_result, i, "jq_type");
889 pfile = PQget(db_result, i, "jq_runonpfile");
890 value = PQget(db_result, i, "jq_args");
891 jq_cmd_args =PQget(db_result, i, "jq_cmd_args");
892
893 if(host != NULL)
894 host = (strlen(host) == 0) ? NULL : host;
895 if(jq_cmd_args != NULL)
896 jq_cmd_args = (strlen(jq_cmd_args) == 0) ? NULL : jq_cmd_args;
897
898 V_DATABASE("DB: jq_pk[%d] added:\n jq_type = %s\n jq_host = %s\n "
899 "jq_runonpfile = %d\n jq_args = %s\n jq_cmd_args = %s\n",
900 j_id, type, host, (pfile != NULL && pfile[0] != '\0'), value, jq_cmd_args);
901
902 /* check if this is a command */
903 if(strcmp(type, "command") == 0)
904 {
905 WARNING("DB: commands in the job queue not implemented,"
906 " using the interface api instead");
907 continue;
908 }
909
910 sprintf(sql, jobsql_information, parent);
911 pri_result = database_exec(scheduler, sql);
912 if(PQresultStatus(pri_result) != PGRES_TUPLES_OK)
913 {
914 PQ_ERROR(pri_result, "database update failed on call to PQexec");
915 continue;
916 }
917 if(PQntuples(pri_result)==0)
918 {
919 WARNING("can not find the user information of job_pk %s\n", parent);
920 SafePQclear(pri_result);
921 continue;
922 }
923 job = job_init(scheduler->job_list, scheduler->job_queue, type, host, j_id,
924 atoi(parent),
925 atoi(PQget(pri_result, 0, "user_pk")),
926 atoi(PQget(pri_result, 0, "group_pk")),
927 atoi(PQget(pri_result, 0, "job_priority")), jq_cmd_args);
928 job_set_data(scheduler, job, value, (pfile && pfile[0] != '\0'));
929
930 SafePQclear(pri_result);
931 }
932
933 SafePQclear(db_result);
934 }
935
936 /**
937 * @brief Resets any jobs in the job queue that are not completed.
938 *
939 * This is to make sure that any jobs that were running with the scheduler
940 * shutdown are run correctly when it starts up again.
941 */
database_reset_queue(scheduler_t * scheduler)942 void database_reset_queue(scheduler_t* scheduler)
943 {
944 PGresult* db_result = database_exec(scheduler, jobsql_resetqueue);
945 if(PQresultStatus(db_result) != PGRES_COMMAND_OK)
946 PQ_ERROR(db_result, "failed to reset job queue");
947 }
948
949 /**
950 * @brief Change the status of a job in the database.
951 *
952 * @param scheduler The scheduler_t* that holds the connection
953 * @param job job_t* object for the job
954 * @param status the new status of the job
955 */
database_update_job(scheduler_t * scheduler,job_t * job,job_status status)956 void database_update_job(scheduler_t* scheduler, job_t* job, job_status status)
957 {
958 /* locals */
959 gchar* sql = NULL;
960 PGresult* db_result;
961 int j_id = job->id;
962 char* message = (job->message == NULL) ? "Failed": job->message;
963
964 /* check how to update database */
965 switch(status)
966 {
967 case JB_NOT_AVAILABLE: case JB_CHECKEDOUT:
968 break;
969 case JB_STARTED:
970 sql = g_strdup_printf(jobsql_started, "localhost", getpid(), j_id);
971 break;
972 case JB_COMPLETE:
973 sql = g_strdup_printf(jobsql_complete, j_id);
974 break;
975 case JB_RESTART:
976 sql = g_strdup_printf(jobsql_restart, j_id);
977 break;
978 case JB_FAILED:
979 sql = g_strdup_printf(jobsql_failed, message, j_id);
980 break;
981 case JB_PAUSED:
982 sql = g_strdup_printf(jobsql_paused, j_id);
983 break;
984 }
985
986 /* update the database job queue */
987 db_result = database_exec(scheduler, sql);
988 if(sql != NULL && PQresultStatus(db_result) != PGRES_COMMAND_OK)
989 PQ_ERROR(db_result, "failed to update job status in job queue");
990
991 if(status == JB_COMPLETE || status == JB_FAILED)
992 email_notification(scheduler, job);
993
994 g_free(sql);
995 }
996
997 /**
998 * @brief Updates the number of items that a job queue entry has processed.
999 *
1000 * @param j_id the id number of the job queue entry
1001 * @param num the number of items processed in total
1002 */
database_job_processed(int j_id,int num)1003 void database_job_processed(int j_id, int num)
1004 {
1005 gchar* sql = NULL;
1006
1007 sql = g_strdup_printf(jobsql_processed, num, j_id);
1008 event_signal(database_exec_event, sql);
1009 }
1010
1011 /**
1012 * @brief Enters the name of the log file for a job into the database
1013 *
1014 * @param j_id the id number for the relevant job
1015 * @param log_name the name of the log file
1016 */
database_job_log(int j_id,char * log_name)1017 void database_job_log(int j_id, char* log_name)
1018 {
1019 gchar* sql = NULL;
1020
1021 sql = g_strdup_printf(jobsql_log, log_name, j_id);
1022 event_signal(database_exec_event, sql);
1023 }
1024
1025 /**
1026 * @brief Changes the priority of a job queue entry in the database.
1027 *
1028 * @param scheduler The scheduler_t* that holds the connection
1029 * @param job the job to change the priority for
1030 * @param priority the new priority of the job
1031 */
database_job_priority(scheduler_t * scheduler,job_t * job,int priority)1032 void database_job_priority(scheduler_t* scheduler, job_t* job, int priority)
1033 {
1034 gchar* sql = NULL;
1035 PGresult* db_result;
1036
1037 sql = g_strdup_printf(jobsql_priority, priority, job->id);
1038 db_result = database_exec(scheduler, sql);
1039 if(sql != NULL && PQresultStatus(db_result) != PGRES_COMMAND_OK)
1040 PQ_ERROR(db_result, "failed to change job queue entry priority");
1041
1042 g_free(sql);
1043 }
1044
1045 /**
1046 * @brief Find s-nail version to check if mta is supported
1047 *
1048 * \return 1 if mta is supported, 0 if not
1049 */
check_mta_support()1050 int check_mta_support()
1051 {
1052 char cmd[] = "dpkg -s s-nail | grep -i 'Version' | awk '{print$2}' | cut -c -4";
1053 char version_str[128];
1054 char buf[128];
1055 float version_float;
1056 FILE *fp;
1057
1058 fp = popen(cmd, "r");
1059 if(!fp)
1060 {
1061 WARNING("Unable to run the command '%s'.\n", cmd);
1062 return 0;
1063 }
1064 while(fgets(buf, sizeof(buf), fp) != NULL)
1065 {
1066 strcpy(version_str,buf);
1067 }
1068 pclose(fp);
1069 sscanf(version_str, "%f", &version_float);
1070
1071 if(version_float - 14.8 > 0.0001)
1072 {
1073 return 1;
1074 }
1075 return 0;
1076 }
1077
1078 /**
1079 * \brief Build command to run to send email
1080 * \param scheduler Current scheduler object
1081 * \param user_email Email id to send mail to
1082 * \return The command to run
1083 */
get_email_command(scheduler_t * scheduler,char * user_email)1084 char* get_email_command(scheduler_t* scheduler, char* user_email)
1085 {
1086 PGresult* db_result_smtp;
1087 int i;
1088 GString* client_cmd;
1089 GHashTable* smtpvariables;
1090 char* temp_smtpvariable;
1091 char* final_command;
1092
1093 db_result_smtp = database_exec(scheduler, smtp_values);
1094 if(PQresultStatus(db_result_smtp) != PGRES_TUPLES_OK || PQntuples(db_result_smtp) == 0)
1095 {
1096 PQ_ERROR(db_result_smtp, "unable to get conf variables for SMTP from sysconfig");
1097 return NULL;
1098 }
1099 client_cmd = g_string_new("");
1100 smtpvariables = g_hash_table_new_full(g_str_hash, g_str_equal, g_free, g_free);
1101 for(i = 0; i < PQntuples(db_result_smtp); i++)
1102 {
1103 if(PQget(db_result_smtp, i, "conf_value")[0]) //Not empty
1104 {
1105 g_hash_table_insert(smtpvariables, g_strdup(PQget(db_result_smtp, i, "variablename")),
1106 g_strdup(PQget(db_result_smtp, i, "conf_value")));
1107 }
1108 }
1109 SafePQclear(db_result_smtp);
1110 if(g_hash_table_contains(smtpvariables, "SMTPHostName") && g_hash_table_contains(smtpvariables, "SMTPPort"))
1111 {
1112 if(g_hash_table_contains(smtpvariables, "SMTPStartTls"))
1113 {
1114 temp_smtpvariable = (char *)g_hash_table_lookup(smtpvariables, "SMTPStartTls");
1115 if(g_strcmp0(temp_smtpvariable, "1") == 0)
1116 {
1117 g_string_append_printf(client_cmd, " -S smtp-use-starttls");
1118 }
1119 }
1120 if(g_hash_table_contains(smtpvariables, "SMTPAuth"))
1121 {
1122 temp_smtpvariable = (char *)g_hash_table_lookup(smtpvariables, "SMTPAuth");
1123 if(g_strcmp0(temp_smtpvariable, "L") == 0)
1124 {
1125 g_string_append_printf(client_cmd, " -S smtp-auth=login");
1126 }
1127 else if(g_strcmp0(temp_smtpvariable, "P") == 0)
1128 {
1129 g_string_append_printf(client_cmd, " -S smtp-auth=plain");
1130 }
1131 else if(g_strcmp0(temp_smtpvariable, "N") == 0)
1132 {
1133 g_string_append_printf(client_cmd, " -S smtp-auth=none");
1134 }
1135 }
1136 if(g_hash_table_contains(smtpvariables, "SMTPFrom"))
1137 {
1138 g_string_append_printf(client_cmd, " -S from=\"%s\"",
1139 (char *)g_hash_table_lookup(smtpvariables, "SMTPFrom"));
1140 }
1141 if(g_hash_table_contains(smtpvariables, "SMTPSslVerify"))
1142 {
1143 temp_smtpvariable = (char *)g_hash_table_lookup(smtpvariables, "SMTPSslVerify");
1144 g_string_append(client_cmd, " -S ssl-verify=");
1145 if(g_strcmp0(temp_smtpvariable, "I") == 0)
1146 {
1147 g_string_append(client_cmd, "ignore");
1148 }
1149 else if(g_strcmp0(temp_smtpvariable, "S") == 0)
1150 {
1151 g_string_append(client_cmd, "strict");
1152 }
1153 else if(g_strcmp0(temp_smtpvariable, "W") == 0)
1154 {
1155 g_string_append(client_cmd, "warn");
1156 }
1157 }
1158 g_string_append_printf(client_cmd, " -S v15-compat");
1159 if(!check_mta_support())
1160 {
1161 g_string_append_printf(client_cmd, " -S smtp=\"");
1162 }
1163 else
1164 {
1165 g_string_append_printf(client_cmd, " -S mta=\"");
1166 }
1167 /* use smtps only if port is not 25 or SMTPStartTls is provided */
1168 if((g_strcmp0((char *)g_hash_table_lookup(smtpvariables, "SMTPPort"), "25") != 0)
1169 || g_strcmp0((char *)g_hash_table_lookup(smtpvariables, "SMTPStartTls"), "1") == 0)
1170 {
1171 g_string_append_printf(client_cmd, "smtps://");
1172 }
1173 else
1174 {
1175 g_string_append_printf(client_cmd, "smtp://");
1176 }
1177 if(g_hash_table_contains(smtpvariables, "SMTPAuthUser"))
1178 {
1179 temp_smtpvariable = g_hash_table_lookup(smtpvariables, "SMTPAuthUser");
1180 g_string_append_uri_escaped(client_cmd, temp_smtpvariable, NULL, TRUE);
1181 if(g_hash_table_lookup(smtpvariables, "SMTPAuthPasswd"))
1182 {
1183 g_string_append_printf(client_cmd, ":");
1184 temp_smtpvariable = g_hash_table_lookup(smtpvariables, "SMTPAuthPasswd");
1185 g_string_append_uri_escaped(client_cmd, temp_smtpvariable, NULL, TRUE);
1186 }
1187 g_string_append_printf(client_cmd, "@");
1188 g_string_append_printf(client_cmd, "%s:%s\"", (char *)g_hash_table_lookup(smtpvariables, "SMTPHostName"),
1189 (char *)g_hash_table_lookup(smtpvariables, "SMTPPort"));
1190 }
1191 temp_smtpvariable = NULL;
1192 final_command = g_strdup_printf(EMAIL_BUILD_CMD, scheduler->email_command,
1193 client_cmd->str, scheduler->email_subject, user_email);
1194 }
1195 else
1196 {
1197 NOTIFY("Unable to send email. SMTP host or port not found in the configuration.\n"
1198 "Please check Configuration Variables.");
1199 final_command = NULL;
1200 }
1201 g_hash_table_destroy(smtpvariables);
1202 g_string_free(client_cmd, TRUE);
1203 return final_command;
1204 }