/*------------------------------------------------------------------------- * Copyright (c) 1999-2004 Kenneth W. Sodemann (stuffle@mac.com) *------------------------------------------------------------------------- * pr_query * * Synopsis: * Handles the creation of problem report selection queries. * * $Id: pr_query.c,v 1.5 2004/11/27 16:32:48 stuffle Exp $ * * This program is free software; you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation; either version 2 of the License, or * (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with this program; if not, write to * Free Software Foundation, Inc. * 59 Temple Place, Suite 330 * Boston, MA 02111-1307 USA *------------------------------------------------------------------------- */ #include #include #include #include #include #include #ifdef USE_NEW_GTK # include #else # ifdef ENABLE_NLS # include # define _(String) gettext (String) # define Q_(String) g_strip_context ((String), gettext (String)) # ifdef gettext_noop # define N_(String) gettext_noop (String) # else # define N_(String) (String) # endif # else /* NLS is disabled */ # define _(String) (String) # define Q_(String) (String) # define N_(String) (String) # define textdomain(String) (String) # define gettext(String) (String) # define dgettext(Domain,String) (String) # define dcgettext(Domain,String,Type) (String) # define bindtextdomain(Domain,Directory) (Domain) # endif #endif /* USE_NEW_GTK */ #include #include "db_utils.h" #include "error_chks.h" #include "prq_utils.h" #define BASIC_SELECT "\ SELECT prj.name, stat.name, pt.name, sevr.name, \ pr.title, stat.order_num, sevr.order_num, \ pr.problem_num, pr.submitter_id, pr.responsible_id, \ pr.creation_date, pr.archived, can_edit_project (prj.project_num) \ FROM project prj, status stat, problem_type pt, \ severity sevr, problem_report pr \ WHERE prj.project_num = pr.project_num \ AND stat.status_num = pr.status_num \ AND sevr.severity_num = pr.severity_num \ AND pt.type_num = pr.type_num " #define BASIC_PRJS "\ AND (prj.project_num in (SELECT project_num \ FROM responsible \ WHERE login_id = '%s') \ OR prj.project_num in (SELECT project_num \ FROM submitter \ WHERE login_id = '%s') \ OR prj.login_id = '%s') " #define ACTIVE_PRJS "AND prj.active = TRUE " #define DEF_ORDER "prj.name, sevr.order_num, stat.order_num " #define IS_RAW_POS 0 #define RAW_POS 1 #define ORDER_POS 2 #define PR_QUERY_SEL "\ SELECT is_raw_sql, raw_sql, order_by \ FROM pr_query \ WHERE query_num = %d" #define ARRAY_SEL "SELECT pr_query.%s[%d] \ FROM pr_query \ WHERE query_num = %d" #define SEVR_NUMS "severity_nums" #define STAT_NUMS "status_nums" #define TYPE_NUMS "problem_type_nums" #define SUB_IDS "submitter_ids" #define RESP_IDS "responsible_ids" /* * pr_query_struct * * This structure holds the data used to build the query. * * There is a NULL definition of this in pr_query.h. This structure * is not meant for public consumption. */ typedef struct pr_query_struct { /* * This is the current user for the query. */ gchar *user_id; /* * The project list is the list of projects that the query applies * to. If this list is empty, all projects for which the user is either * listed as a submitter, or responsible memeber will be used. * * The project list applies to raw and non-raw query types (see below). */ GList *projects; /* * The user is allowed to either use the UI to select * query criteria, or just right some raw query criteria. */ gboolean is_raw; gchar *raw_sql; /* * These lists represent the items the user can restrict the * non-raw queries by. */ GList *severities; GList *statuses; GList *problem_types; GList *submitter_ids; GList *responsible_ids; /* * Limit the query to only active entities? */ gboolean active_prjs; /* * The order_by clause also only applies to non-raw queries. */ gchar *order_by; } pr_query_struct; void set_raw_pr_where (pr_query_struct *q, const gchar *str) { assert (q); q->raw_sql = (gchar *)g_malloc (strlen (str) + 1); strcpy (q->raw_sql, str); q->is_raw = TRUE; } void clear_raw_pr_where (pr_query_struct *q) { assert (q); g_free (q->raw_sql); q->raw_sql = NULL; q->is_raw = FALSE; } /* * ORDER BY handlers. */ void set_order_by (pr_query_struct *q, const gchar *str) { assert (q); q->order_by = (gchar *)g_malloc (strlen (str) + 1); strcpy (q->order_by, str); } void clear_order_by (pr_query_struct *q) { assert (q); g_free (q->order_by); q->order_by = NULL; } /* * clear_xxxx_restrictions */ void clear_project_restrictions (pr_query_struct *q) { assert (q); g_list_free (q->projects); q->projects = NULL; return; } void clear_severity_restrictions (pr_query_struct *q) { assert (q); g_list_free (q->severities); q->severities = NULL; return; } void clear_status_restrictions (pr_query_struct *q) { assert (q); g_list_free (q->statuses); q->statuses = NULL; return; } void clear_problem_type_restrictions (pr_query_struct *q) { assert (q); g_list_free (q->problem_types); q->problem_types = NULL; return; } /* * The submitter_ids and responsibles_ids lists contain data we allocated * space for in the add_... */ void clear_submitter_restrictions (pr_query_struct *q) { GList *t_list = g_list_first (q->submitter_ids); assert (q); while (t_list) { g_free (t_list->data); t_list = t_list->next; } g_list_free (q->submitter_ids); q->submitter_ids = NULL; return; } void clear_responsible_restrictions (pr_query_struct *q) { GList *t_list = g_list_first (q->responsible_ids); assert (q); while (t_list) { g_free (t_list->data); t_list = t_list->next; } g_list_free (q->responsible_ids); q->responsible_ids = NULL; return; } /* * create_pr_query * * Create the default PR query */ pr_query_struct * create_pr_query (gchar *user_id) { pr_query_struct *t_pqs; assert (user_id); t_pqs = (pr_query_struct *)g_malloc (sizeof (pr_query_struct)); /* * The default query has everything NULL/FALSE except the * user_id and the order_by. */ t_pqs->user_id = (gchar *)g_malloc (strlen (user_id) + 1); strcpy (t_pqs->user_id, user_id); t_pqs->projects = NULL; t_pqs->severities = NULL; t_pqs->statuses = NULL; t_pqs->problem_types = NULL; t_pqs->submitter_ids = NULL; t_pqs->responsible_ids = NULL; t_pqs->raw_sql = NULL; t_pqs->is_raw = FALSE; t_pqs->active_prjs = FALSE; set_order_by (t_pqs, DEF_ORDER); return t_pqs; } pr_query_struct * create_pr_query_from_table (PGconn *conn, const gchar *user_id, gint query_pk) { pr_query_struct *t_pqs; GString *sql_buffer; PGresult *res; assert (conn); assert (user_id); t_pqs = (pr_query_struct *)g_malloc (sizeof (pr_query_struct)); t_pqs->user_id = (gchar *)g_malloc (strlen (user_id) + 1); strcpy (t_pqs->user_id, user_id); /* * Build the list data. Projects are NULL since that data is * not stored in the database. Rather, it is selected at run * time. */ t_pqs->projects = NULL; t_pqs->severities = create_num_list (conn, query_pk, SEVR_NUMS); t_pqs->statuses = create_num_list (conn, query_pk, STAT_NUMS); t_pqs->problem_types = create_num_list (conn, query_pk, TYPE_NUMS); t_pqs->submitter_ids = create_txt_list (conn, query_pk, SUB_IDS); t_pqs->responsible_ids = create_txt_list (conn, query_pk, RESP_IDS); /* * The lists where processed one at a time. Now build a query * to get the remainder of the data for this query. That should * just be the raw SQL handling stuff and the "ORDER BY" clause. */ sql_buffer = g_string_new (""); g_string_sprintf (sql_buffer, PR_QUERY_SEL, query_pk); res = PQexec (conn, sql_buffer->str); if (PQntuples (res) == 0) { t_pqs->is_raw = FALSE; t_pqs->raw_sql = NULL; set_order_by (t_pqs, DEF_ORDER); } else { /* raw SQL */ t_pqs->is_raw = (toupper ((PQgetvalue (res, 0, IS_RAW_POS))[0]) == 'T'); if (PQgetisnull (res, 0, RAW_POS)) { t_pqs->raw_sql = NULL; } else { t_pqs->raw_sql = (gchar *)g_malloc (strlen (PQgetvalue (res, 0, RAW_POS)) + 1); strcpy (t_pqs->raw_sql, PQgetvalue (res, 0, RAW_POS)); } /* ORDER BY info */ if (PQgetisnull (res, 0, ORDER_POS)) { set_order_by (t_pqs, DEF_ORDER); } else { set_order_by (t_pqs, PQgetvalue (res, 0, ORDER_POS)); } } PQclear (res); g_string_free (sql_buffer, TRUE); return t_pqs; } pr_query_struct * create_pr_query_from_table_li (const gchar *conn_str, const gchar *user_id, gint query_pk) { PGconn *conn; pr_query_struct *pqs = NULL; conn = PQconnectdb (conn_str); if (PQstatus (conn) == CONNECTION_OK) { pqs = create_pr_query_from_table (conn, user_id, query_pk); } else { syslog (LOG_ERR, "create_pr_query_from_table_li db login failed: %s", PQerrorMessage (conn)); } PQfinish (conn); return pqs; } void destroy_pr_query (pr_query_struct *pr_query) { /* * Use the defined functions to clear everthing. They all * have the right amount of GC built in. */ clear_project_restrictions (pr_query); clear_severity_restrictions (pr_query); clear_status_restrictions (pr_query); clear_problem_type_restrictions (pr_query); clear_submitter_restrictions (pr_query); clear_responsible_restrictions (pr_query); clear_raw_pr_where (pr_query); clear_order_by (pr_query); /* This one does not have its own "clear" function. */ g_free (pr_query->user_id); g_free (pr_query); return; } void restrict_to_active_projects (pr_query_struct *q, gboolean flag) { q->active_prjs = flag; } /* * add_xxxx_restriction functions * * In each case, search for the item in the current list, and add * the item if it is not found. */ void add_project_restriction (pr_query_struct *q, gint pk) { assert (q); if (!g_list_find (q->projects, GINT_TO_POINTER (pk))) { q->projects = g_list_append (q->projects, GINT_TO_POINTER (pk)); /* * is_raw is NOT set to FALSE in this case, since the user can * add project restrictions to raw as well as "simple" queries. */ } return; } void add_severity_restriction (pr_query_struct *q, gint pk) { assert (q); if (!g_list_find (q->severities, GINT_TO_POINTER (pk))) { q->severities = g_list_append (q->severities, GINT_TO_POINTER (pk)); q->is_raw = FALSE; } return; } void add_status_restriction (pr_query_struct *q, gint pk) { assert (q); if (!g_list_find (q->statuses, GINT_TO_POINTER (pk))) { q->statuses = g_list_append (q->statuses, GINT_TO_POINTER (pk)); q->is_raw = FALSE; } return; } void add_problem_type_restriction (pr_query_struct *q, gint pk) { assert (q); if (!g_list_find (q->problem_types, GINT_TO_POINTER (pk))) { q->problem_types = g_list_append (q->problem_types, GINT_TO_POINTER (pk)); q->is_raw = FALSE; } return; } /* * for the add_submitter_restrictions and add_responsible_restrictions, * make own copy of id in case the one passed in goes out of scope * before we use the query. */ void add_submitter_restriction (pr_query_struct *q, gchar *id) { gchar *buffer; assert (q); assert (id); if (!g_list_find (q->submitter_ids, id)) { buffer = (gchar *)g_malloc (strlen (id) + 1); strcpy (buffer, id); q->submitter_ids = g_list_append (q->submitter_ids, buffer); q->is_raw = FALSE; } return; } void add_responsible_restriction (pr_query_struct *q, gchar *id) { gchar *buffer; assert (q); assert (id); if (!g_list_find (q->responsible_ids, id)) { buffer = (gchar *)g_malloc (strlen (id) + 1); strcpy (buffer, id); q->responsible_ids = g_list_append (q->responsible_ids, buffer); q->is_raw = FALSE; } return; } /* * pr_query_string * * Create the SQL query string. */ GString * create_query_string (const pr_query_struct *q, gboolean show_arch) { GString *buffer; GList *the_list; GString *scratchpad; /* * The first part of the query is always the same. */ buffer = g_string_new (BASIC_SELECT); scratchpad = g_string_new (""); /* * If show_arch is FALSE, limit to only non-archived items. * If show_arch is TRUE, we will return any problem report * regardless of whether or not it is archived. */ if (!show_arch) { buffer = g_string_append (buffer, "AND pr.archived = false "); } /* * Check for project restrictions. If there are none, use the * basic ones. */ if (q->projects) { the_list = g_list_first (q->projects); buffer = g_string_append (buffer, " AND pr.project_num in ("); while (the_list) { g_string_sprintf (scratchpad, "%d, ", GPOINTER_TO_INT (the_list->data)); buffer = g_string_append (buffer, scratchpad->str); the_list = the_list->next; } /* * we will need to replace the last two chars (", ") * with a closing paren. */ buffer = g_string_truncate (buffer, buffer->len - 2); buffer = g_string_append (buffer, ") "); } else { g_string_sprintf (scratchpad, BASIC_PRJS, q->user_id, q->user_id, q->user_id); buffer = g_string_append (buffer, scratchpad->str); } if (q->active_prjs) { buffer = g_string_append (buffer, ACTIVE_PRJS); } /* * The rest of the restrictions on the query depend upon * whether or not there is raw SQL. */ if (q->is_raw) { if (q->raw_sql != NULL) { buffer = g_string_append (buffer, q->raw_sql); } } else { if (q->severities) { the_list = g_list_first (q->severities); buffer = g_string_append (buffer, " AND pr.severity_num in ("); while (the_list) { g_string_sprintf (scratchpad, "%d, ", GPOINTER_TO_INT (the_list->data)); buffer = g_string_append (buffer, scratchpad->str); the_list = the_list->next; } /* * Replacing last two chars (", "), with an ending paren. */ buffer = g_string_truncate (buffer, buffer->len - 2); buffer = g_string_append (buffer, ") "); } if (q->statuses) { the_list = g_list_first (q->statuses); buffer = g_string_append (buffer, " AND pr.status_num in ("); while (the_list) { g_string_sprintf (scratchpad, "%d, ", GPOINTER_TO_INT (the_list->data)); buffer = g_string_append (buffer, scratchpad->str); the_list = the_list->next; } /* * Replacing the last two chars (", "), with an ending paren. */ buffer = g_string_truncate (buffer, buffer->len - 2); buffer = g_string_append (buffer, ") "); } if (q->problem_types) { the_list = g_list_first (q->problem_types); buffer = g_string_append (buffer, " AND pr.type_num in ("); while (the_list) { g_string_sprintf (scratchpad, "%d, ", GPOINTER_TO_INT (the_list->data)); buffer = g_string_append (buffer, scratchpad->str); the_list = the_list->next; } /* * Replacing the last two chars (", "), with an ending paren. */ buffer = g_string_truncate (buffer, buffer->len - 2); buffer = g_string_append (buffer, ") "); } if (q->submitter_ids) { the_list = g_list_first (q->submitter_ids); buffer = g_string_append (buffer, " AND pr.submitter_id in ("); while (the_list) { g_string_sprintf (scratchpad, "'%s', ", (gchar *)the_list->data); buffer = g_string_append (buffer, scratchpad->str); the_list = the_list->next; } /* * Replacing the last two chars (", "), with an ending paren. */ buffer = g_string_truncate (buffer, buffer->len - 2); buffer = g_string_append (buffer, ") "); } if (q->responsible_ids) { the_list = g_list_first (q->responsible_ids); buffer = g_string_append (buffer, " AND pr.responsible_id in ("); while (the_list) { g_string_sprintf (scratchpad, "'%s', ", (gchar *)the_list->data); buffer = g_string_append (buffer, scratchpad->str); the_list = the_list->next; } /* * Replacing the last two chars (", "), with an ending paren. */ buffer = g_string_truncate (buffer, buffer->len - 2); buffer = g_string_append (buffer, ") "); } if (q->order_by) { buffer = g_string_append (buffer, " ORDER BY "); buffer = g_string_append (buffer, q->order_by); } } g_string_free (scratchpad, TRUE); return buffer; } void set_problem_report_archived (PGconn *conn, gint pk, gboolean archived) { GString *sql; PGresult *res; START_TRANSACTION (conn); sql = g_string_new ("UPDATE problem_report SET archived = "); if (archived) { sql = g_string_append (sql, "TRUE "); } else { sql = g_string_append (sql, "FALSE "); } g_string_append_printf (sql, "WHERE problem_num = %d", pk); res = PQexec (conn, sql->str); chk_sql_error (res, _("Set archive flag")); finalize_transaction (conn, res); g_string_free (sql, TRUE); PQclear (res); }