C++ db_sql_query函数代码示例

/*
 * Prune Job stat records from the database.
 */
static bool prune_stats(UAContext *ua, utime_t retention)
{
   char ed1[50];
   char dt[MAX_TIME_LENGTH];
   POOL_MEM query(PM_MESSAGE);
   utime_t now = (utime_t)time(NULL);

   db_lock(ua->db);
   Mmsg(query, "DELETE FROM JobHisto WHERE JobTDate < %s",
        edit_int64(now - retention, ed1));
   db_sql_query(ua->db, query.c_str());
   db_unlock(ua->db);

   ua->info_msg(_("Pruned Jobs from JobHisto in catalog.\n"));

   bstrutime(dt, sizeof(dt), now - retention);

   db_lock(ua->db);
   Mmsg(query, "DELETE FROM DeviceStats WHERE SampleTime < '%s'", dt);
   db_sql_query(ua->db, query.c_str());
   db_unlock(ua->db);

   ua->info_msg(_("Pruned Statistics from DeviceStats in catalog.\n"));

   db_lock(ua->db);
   Mmsg(query, "DELETE FROM JobStats WHERE SampleTime < '%s'", dt);
   db_sql_query(ua->db, query.c_str());
   db_unlock(ua->db);

   ua->info_msg(_("Pruned Statistics from JobStats in catalog.\n"));

   return true;
}

/*
 * This routine will purge (delete) all records
 * associated with a particular Volume. It will
 * not delete the media record itself.
 * TODO: This function is broken and it doesn't purge
 *       File, BaseFiles, Log, ...
 *       We call it from relabel and delete volume=, both ensure
 *       that the volume is properly purged.
 */
static int do_media_purge(B_DB *mdb, MEDIA_DBR *mr)
{
   POOLMEM *query = get_pool_memory(PM_MESSAGE);
   struct s_del_ctx del;
   char ed1[50];
   int i;

   del.num_ids = 0;
   del.tot_ids = 0;
   del.num_del = 0;
   del.max_ids = 0;
   Mmsg(mdb->cmd, "SELECT JobId from JobMedia WHERE MediaId=%d", mr->MediaId);
   del.max_ids = mr->VolJobs;
   if (del.max_ids < 100) {
      del.max_ids = 100;
   } else if (del.max_ids > MAX_DEL_LIST_LEN) {
      del.max_ids = MAX_DEL_LIST_LEN;
   }
   del.JobId = (JobId_t *)malloc(sizeof(JobId_t) * del.max_ids);
   db_sql_query(mdb, mdb->cmd, delete_handler, (void *)&del);

   for (i=0; i < del.num_ids; i++) {
      Dmsg1(400, "Delete JobId=%d\n", del.JobId[i]);
      Mmsg(query, "DELETE FROM Job WHERE JobId=%s", edit_int64(del.JobId[i], ed1));
      db_sql_query(mdb, query);
      Mmsg(query, "DELETE FROM File WHERE JobId=%s", edit_int64(del.JobId[i], ed1));
      db_sql_query(mdb, query);
      Mmsg(query, "DELETE FROM JobMedia WHERE JobId=%s", edit_int64(del.JobId[i], ed1));
      db_sql_query(mdb, query);
   }
   free(del.JobId);
   free_pool_memory(query);
   return 1;
}

/*
 * Remove all records from catalog for a list of JobIds
 */
void purge_jobs_from_catalog(UAContext *ua, char *jobs)
{
   POOL_MEM query(PM_MESSAGE);

   /* Delete (or purge) records associated with the job */
   purge_files_from_jobs(ua, jobs);

   Mmsg(query, "DELETE FROM JobMedia WHERE JobId IN (%s)", jobs);
   db_sql_query(ua->db, query.c_str());
   Dmsg1(050, "Delete JobMedia sql=%s\n", query.c_str());

   Mmsg(query, "DELETE FROM Log WHERE JobId IN (%s)", jobs);
   db_sql_query(ua->db, query.c_str());
   Dmsg1(050, "Delete Log sql=%s\n", query.c_str());

   Mmsg(query, "DELETE FROM RestoreObject WHERE JobId IN (%s)", jobs);
   db_sql_query(ua->db, query.c_str());
   Dmsg1(050, "Delete RestoreObject sql=%s\n", query.c_str());

   Mmsg(query, "DELETE FROM PathVisibility WHERE JobId IN (%s)", jobs);
   db_sql_query(ua->db, query.c_str());
   Dmsg1(050, "Delete PathVisibility sql=%s\n", query.c_str());

   upgrade_copies(ua, jobs);

   /* Now remove the Job record itself */
   Mmsg(query, "DELETE FROM Job WHERE JobId IN (%s)", jobs);
   db_sql_query(ua->db, query.c_str());

   Dmsg1(050, "Delete Job sql=%s\n", query.c_str());
}

static bool create_temp_tables(UAContext *ua)
{
   /* Create temp tables and indicies */
   if (!db_sql_query(ua->db, create_deltabs[db_get_type_index(ua->db)])) {
      ua->error_msg("%s", db_strerror(ua->db));
      Dmsg0(050, "create DelTables table failed\n");
      return false;
   }
   if (!db_sql_query(ua->db, create_delindex)) {
       ua->error_msg("%s", db_strerror(ua->db));
       Dmsg0(050, "create DelInx1 index failed\n");
       return false;
   }
   return true;
}

/*
 * For a given file (path+filename), split into path and file, then
 *   lookup the most recent backup in the catalog to get the JobId
 *   and FileIndex, then insert them into the findex list.
 */
static bool insert_file_into_findex_list(UAContext *ua, RESTORE_CTX *rx, char *file,
                                        char *date)
{
   strip_trailing_newline(file);
   split_path_and_filename(ua, rx, file);
   if (*rx->JobIds == 0) {
      Mmsg(rx->query, uar_jobid_fileindex, date, rx->path, rx->fname,
           rx->ClientName);
   } else {
      Mmsg(rx->query, uar_jobids_fileindex, rx->JobIds, date,
           rx->path, rx->fname, rx->ClientName);
   }
   rx->found = false;
   /* Find and insert jobid and File Index */
   if (!db_sql_query(ua->db, rx->query, jobid_fileindex_handler, (void *)rx)) {
      ua->error_msg(_("Query failed: %s. ERR=%s\n"),
         rx->query, db_strerror(ua->db));
   }
   if (!rx->found) {
      ua->error_msg(_("No database record found for: %s\n"), file);
//    ua->error_msg("Query=%s\n", rx->query);
      return true;
   }
   return true;
}

static void drop_temp_tables(UAContext *ua)
{
   int i;
   for (i=0; drop_deltabs[i]; i++) {
      db_sql_query(ua->db, drop_deltabs[i]);
   }
}

/*
 * Get prune list for a volume
 */
int get_prune_list_for_volume(UAContext *ua, MEDIA_DBR *mr, del_ctx *del)
{
   POOL_MEM query(PM_MESSAGE);
   int count = 0;
   utime_t now, period;
   char ed1[50], ed2[50];

   if (mr->Enabled == 2) {
      return 0;                    /* cannot prune Archived volumes */
   }

   /*
    * Now add to the  list of JobIds for Jobs written to this Volume
    */
   edit_int64(mr->MediaId, ed1); 
   period = mr->VolRetention;
   now = (utime_t)time(NULL);
   edit_int64(now-period, ed2);
   Mmsg(query, sel_JobMedia, ed1, ed2);
   Dmsg3(250, "Now=%d period=%d now-period=%s\n", (int)now, (int)period,
      ed2);

   Dmsg1(050, "Query=%s\n", query.c_str());
   if (!db_sql_query(ua->db, query.c_str(), file_delete_handler, (void *)del)) {
      if (ua->verbose) {
         ua->error_msg("%s", db_strerror(ua->db));
      }
      Dmsg0(050, "Count failed\n");
      goto bail_out;
   }
   count = exclude_running_jobs_from_list(del);
   
bail_out:
   return count;
}

/*
 * Check catalog max_connections setting
 */
bool db_check_max_connections(JCR *jcr, B_DB *mdb, uint32_t max_concurrent_jobs)
{
   struct max_connections_context context;

   /* Without Batch insert, no need to verify max_connections */
   if (!mdb->batch_insert_available())
      return true;

   context.db = mdb;
   context.nr_connections = 0;

   /* Check max_connections setting */
   if (!db_sql_query(mdb, sql_get_max_connections[db_get_type_index(mdb)],
                     db_max_connections_handler, &context)) {
      Jmsg(jcr, M_ERROR, 0, "Can't verify max_connections settings %s", mdb->errmsg);
      return false;
   }
   if (context.nr_connections && max_concurrent_jobs && max_concurrent_jobs > context.nr_connections) {
      Mmsg(mdb->errmsg,
           _("Potential performance problem:\n"
             "max_connections=%d set for %s database \"%s\" should be larger than Director's "
             "MaxConcurrentJobs=%d\n"),
           context.nr_connections, db_get_type(mdb), mdb->get_db_name(), max_concurrent_jobs);
      Jmsg(jcr, M_WARNING, 0, "%s", mdb->errmsg);
      return false;
   }

   return true;
}

/* Modify the RecyclePool of a Volume */
void update_vol_recyclepool(UAContext *ua, char *val, MEDIA_DBR *mr)
{
   POOL_DBR pr;
   POOL_MEM query(PM_MESSAGE);
   char ed1[50], ed2[50], *poolname;

   if(val && *val) { /* update volume recyclepool="Scratch" */
     /* If a pool name is given, look up the PoolId */
     memset(&pr, 0, sizeof(pr));
     bstrncpy(pr.Name, val, sizeof(pr.Name));
     if (!get_pool_dbr(ua, &pr, NT_("recyclepool"))) {
        return;
     }
     /* pool = select_pool_resource(ua);  */
     mr->RecyclePoolId = pr.PoolId;            /* get the PoolId */
     poolname = pr.Name;

  } else { /* update volume recyclepool="" */
    /* If no pool name is given, set the PoolId to 0 (the default) */
     mr->RecyclePoolId = 0;
     poolname = _("*None*");
  }

   db_lock(ua->db);
   Mmsg(query, "UPDATE Media SET RecyclePoolId=%s WHERE MediaId=%s",
      edit_int64(mr->RecyclePoolId, ed1), edit_int64(mr->MediaId, ed2));
   if (!db_sql_query(ua->db, query.c_str(), NULL, NULL)) {
      ua->error_msg("%s", db_strerror(ua->db));
   } else {
      ua->info_msg(_("New RecyclePool is: %s\n"), poolname);
   }
   db_unlock(ua->db);
}

/*
 * This routine returns:
 *    false       if an error occurred
 *    true        otherwise
 *    ids.count   number of jobids found (may be zero)
 */
static bool find_jobids_of_pool_uncopied_jobs(JCR *jcr, idpkt *ids)
{
   bool ok = false;
   POOL_MEM query(PM_MESSAGE);

   /* Only a copy job is allowed */
   if (jcr->getJobType() != JT_COPY) {
      Jmsg(jcr, M_FATAL, 0,
           _("Selection Type 'pooluncopiedjobs' only applies to Copy Jobs"));
      goto bail_out;
   }

   Dmsg1(dbglevel, "copy selection pattern=%s\n", jcr->rpool->name());
   Mmsg(query, sql_jobids_of_pool_uncopied_jobs, jcr->rpool->name());
   Dmsg1(dbglevel, "get uncopied jobs query=%s\n", query.c_str());
   if (!db_sql_query(jcr->db, query.c_str(), unique_dbid_handler, (void *)ids)) {
      Jmsg(jcr, M_FATAL, 0,
           _("SQL to get uncopied jobs failed. ERR=%s\n"), db_strerror(jcr->db));
      goto bail_out;
   }
   ok = true;

bail_out:
   return ok;
}

static bool build_directory_tree(UAContext *ua, RESTORE_CTX *rx)
{
   TREE_CTX tree;
   JobId_t JobId, last_JobId;
   char *p;
   bool OK = true;
   char ed1[50];

   memset(&tree, 0, sizeof(TREE_CTX));
   /*
    * Build the directory tree containing JobIds user selected
    */
   tree.root = new_tree(rx->TotalFiles);
   tree.ua = ua;
   tree.all = rx->all;
   last_JobId = 0;
   /*
    * For display purposes, the same JobId, with different volumes may
    * appear more than once, however, we only insert it once.
    */
   p = rx->JobIds;
   tree.FileEstimate = 0;
   if (get_next_jobid_from_list(&p, &JobId) > 0) {
      /* Use first JobId as estimate of the number of files to restore */
      Mmsg(rx->query, uar_count_files, edit_int64(JobId, ed1));
      if (!db_sql_query(ua->db, rx->query, restore_count_handler, (void *)rx)) {
         ua->error_msg("%s\n", db_strerror(ua->db));
      }
      if (rx->found) {
         /* Add about 25% more than this job for over estimate */
         tree.FileEstimate = rx->JobId + (rx->JobId >> 2);
         tree.DeltaCount = rx->JobId/50; /* print 50 ticks */
      }

/* Modify the Pool in which this Volume is located */
void update_vol_pool(UAContext *ua, char *val, MEDIA_DBR *mr, POOL_DBR *opr)
{
   POOL_DBR pr;
   POOL_MEM query(PM_MESSAGE);
   char ed1[50], ed2[50];

   memset(&pr, 0, sizeof(pr));
   bstrncpy(pr.Name, val, sizeof(pr.Name));
   if (!get_pool_dbr(ua, &pr)) {
      return;
   }
   mr->PoolId = pr.PoolId;            /* set new PoolId */
   /*
    */
   db_lock(ua->db);
   Mmsg(query, "UPDATE Media SET PoolId=%s WHERE MediaId=%s",
      edit_int64(mr->PoolId, ed1), edit_int64(mr->MediaId, ed2));
   if (!db_sql_query(ua->db, query.c_str(), NULL, NULL)) {
      ua->error_msg("%s", db_strerror(ua->db));
   } else {
      ua->info_msg(_("New Pool is: %s\n"), pr.Name);
      opr->NumVols--;
      if (!db_update_pool_record(ua->jcr, ua->db, opr)) {
         ua->error_msg("%s", db_strerror(ua->db));
      }
      pr.NumVols++;
      if (!db_update_pool_record(ua->jcr, ua->db, &pr)) {
         ua->error_msg("%s", db_strerror(ua->db));
      }
   }
   db_unlock(ua->db);
}

static void update_volmaxfiles(UAContext *ua, char *val, MEDIA_DBR *mr)
{
   POOL_MEM query(PM_MESSAGE);
   char ed1[50];
   Mmsg(query, "UPDATE Media SET MaxVolFiles=%s WHERE MediaId=%s",
      val, edit_int64(mr->MediaId, ed1));
   if (!db_sql_query(ua->db, query.c_str(), NULL, NULL)) {
      ua->error_msg("%s", db_strerror(ua->db));
   } else {
      ua->info_msg(_("New max files is: %s\n"), val);
   }
}

/*
 * Change the type of the next copy job to backup.
 * We need to upgrade the next copy of a normal job,
 * and also upgrade the next copy when the normal job
 * already have been purged.
 *
 *   JobId: 1   PriorJobId: 0    (original)
 *   JobId: 2   PriorJobId: 1    (first copy)
 *   JobId: 3   PriorJobId: 1    (second copy)
 *
 *   JobId: 2   PriorJobId: 1    (first copy, now regular backup)
 *   JobId: 3   PriorJobId: 1    (second copy)
 *
 *  => Search through PriorJobId in jobid and
 *                    PriorJobId in PriorJobId (jobid)
 */
void upgrade_copies(UAContext *ua, char *jobs)
{
   POOL_MEM query(PM_MESSAGE);

   db_lock(ua->db);

   /* Do it in two times for mysql */
   Mmsg(query, uap_upgrade_copies_oldest_job[db_get_type_index(ua->db)], JT_JOB_COPY, jobs, jobs);
   db_sql_query(ua->db, query.c_str());
   Dmsg1(050, "Upgrade copies Log sql=%s\n", query.c_str());

   /* Now upgrade first copy to Backup */
   Mmsg(query, "UPDATE Job SET Type='B' "      /* JT_JOB_COPY => JT_BACKUP  */
                "WHERE JobId IN ( SELECT JobId FROM cpy_tmp )");

   db_sql_query(ua->db, query.c_str());

   Mmsg(query, "DROP TABLE cpy_tmp");
   db_sql_query(ua->db, query.c_str());

   db_unlock(ua->db);
}

/*
 * Remove File records from a list of JobIds
 */
void purge_files_from_jobs(UAContext *ua, char *jobs)
{
   POOL_MEM query(PM_MESSAGE);

   Mmsg(query, "DELETE FROM File WHERE JobId IN (%s)", jobs);
   db_sql_query(ua->db, query.c_str());
   Dmsg1(050, "Delete File sql=%s\n", query.c_str());

   Mmsg(query, "DELETE FROM BaseFiles WHERE JobId IN (%s)", jobs);
   db_sql_query(ua->db, query.c_str());
   Dmsg1(050, "Delete BaseFiles sql=%s\n", query.c_str());

   /*
    * Now mark Job as having files purged. This is necessary to
    * avoid having too many Jobs to process in future prunings. If
    * we don't do this, the number of JobId's in our in memory list
    * could grow very large.
    */
   Mmsg(query, "UPDATE Job SET PurgedFiles=1 WHERE JobId IN (%s)", jobs);
   db_sql_query(ua->db, query.c_str());
   Dmsg1(050, "Mark purged sql=%s\n", query.c_str());
}