2 Bacula® - The Network Backup Solution
4 Copyright (C) 2009-2010 Free Software Foundation Europe e.V.
6 The main author of Bacula is Kern Sibbald, with contributions from
7 many others, a complete list can be found in the file AUTHORS.
8 This program is Free Software; you can redistribute it and/or
9 modify it under the terms of version three of the GNU Affero General Public
10 License as published by the Free Software Foundation, which is
11 listed in the file LICENSE.
13 This program is distributed in the hope that it will be useful, but
14 WITHOUT ANY WARRANTY; without even the implied warranty of
15 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
16 General Public License for more details.
18 You should have received a copy of the GNU Affero General Public License
19 along with this program; if not, write to the Free Software
20 Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
23 Bacula® is a registered trademark of Kern Sibbald.
24 The licensor of Bacula is the Free Software Foundation Europe
25 (FSFE), Fiduciary Program, Sumatrastrasse 25, 8006 Zürich,
26 Switzerland, email:ftf@fsfeurope.org.
29 #define __SQL_C /* indicate that this is sql.c */
32 #include "cats/cats.h"
33 #include "lib/htable.h"
37 #define dbglevel_sql 15
39 static int result_handler(void *ctx, int fields, char **row)
42 Pmsg4(0, "%s\t%s\t%s\t%s\n",
43 row[0], row[1], row[2], row[3]);
44 } else if (fields == 5) {
45 Pmsg5(0, "%s\t%s\t%s\t%s\t%s\n",
46 row[0], row[1], row[2], row[3], row[4]);
47 } else if (fields == 6) {
48 Pmsg6(0, "%s\t%s\t%s\t%s\t%s\t%s\n",
49 row[0], row[1], row[2], row[3], row[4], row[5]);
50 } else if (fields == 7) {
51 Pmsg7(0, "%s\t%s\t%s\t%s\t%s\t%s\t%s\n",
52 row[0], row[1], row[2], row[3], row[4], row[5], row[6]);
57 Bvfs::Bvfs(JCR *j, B_DB *mdb) {
60 db = mdb; /* need to inc ref count */
61 jobids = get_pool_memory(PM_NAME);
62 prev_dir = get_pool_memory(PM_NAME);
63 pattern = get_pool_memory(PM_NAME);
64 *jobids = *prev_dir = *pattern = 0;
65 dir_filenameid = pwd_id = offset = 0;
66 see_copies = see_all_versions = false;
69 list_entries = result_handler;
75 free_pool_memory(jobids);
76 free_pool_memory(pattern);
77 free_pool_memory(prev_dir);
85 void Bvfs::filter_jobid()
91 /* Query used by Bweb to filter clients, activated when using
96 "SELECT DISTINCT JobId FROM Job JOIN Client USING (ClientId) "
97 "JOIN (SELECT ClientId FROM client_group_member "
98 "JOIN client_group USING (client_group_id) "
99 "JOIN bweb_client_group_acl USING (client_group_id) "
100 "JOIN bweb_user USING (userid) "
101 "WHERE bweb_user.username = '%s' "
102 ") AS filter USING (ClientId) "
103 " WHERE JobId IN (%s)",
107 Dmsg1(dbglevel_sql, "q=%s\n", query.c_str());
108 db_sql_query(db, query.c_str(), db_list_handler, &ctx);
109 pm_strcpy(jobids, ctx.list);
112 void Bvfs::set_jobid(JobId_t id)
114 Mmsg(jobids, "%lld", (uint64_t)id);
118 void Bvfs::set_jobids(char *ids)
120 pm_strcpy(jobids, ids);
125 * TODO: Find a way to let the user choose how he wants to display
126 * files and directories
131 * Working Object to store PathId already seen (avoid
132 * database queries), equivalent to %cache_ppathid in perl
144 htable *cache_ppathid;
149 cache_ppathid = (htable *)malloc(sizeof(htable));
150 cache_ppathid->init(&link, &link, NITEMS);
152 nodes = (hlink *) malloc(max_node * sizeof (hlink));
154 table_node = New(alist(5, owned_by_alist));
155 table_node->append(nodes);
159 if (++nb_node >= max_node) {
161 nodes = (hlink *)malloc(max_node * sizeof(hlink));
162 table_node->append(nodes);
164 return nodes + nb_node;
167 bool lookup(char *pathid) {
168 bool ret = cache_ppathid->lookup(pathid) != NULL;
172 void insert(char *pathid) {
173 hlink *h = get_hlink();
174 cache_ppathid->insert(pathid, h);
178 cache_ppathid->destroy();
183 pathid_cache(const pathid_cache &); /* prohibit pass by value */
184 pathid_cache &operator= (const pathid_cache &);/* prohibit class assignment*/
187 /* Return the parent_dir with the trailing / (update the given string)
188 * TODO: see in the rest of bacula if we don't have already this function
194 char *bvfs_parent_dir(char *path)
197 int len = strlen(path) - 1;
199 if (len >= 0 && path[len] == '/') { /* if directory, skip last / */
205 while (p > path && !IsPathSeparator(*p)) {
213 /* Return the basename of the with the trailing /
214 * TODO: see in the rest of bacula if we don't have
215 * this function already
217 char *bvfs_basename_dir(char *path)
220 int len = strlen(path) - 1;
222 if (path[len] == '/') { /* if directory, skip last / */
228 while (p > path && !IsPathSeparator(*p)) {
232 p++; /* skip first / */
238 static void build_path_hierarchy(JCR *jcr, B_DB *mdb,
239 pathid_cache &ppathid_cache,
240 char *org_pathid, char *path)
242 Dmsg1(dbglevel, "build_path_hierarchy(%s)\n", path);
245 char *bkp = mdb->path;
246 strncpy(pathid, org_pathid, sizeof(pathid));
248 /* Does the ppathid exist for this ? we use a memory cache... In order to
249 * avoid the full loop, we consider that if a dir is allready in the
250 * PathHierarchy table, then there is no need to calculate all the
253 while (path && *path)
255 if (!ppathid_cache.lookup(pathid))
258 "SELECT PPathId FROM PathHierarchy WHERE PathId = %s",
261 QUERY_DB(jcr, mdb, mdb->cmd);
262 /* Do we have a result ? */
263 if (sql_num_rows(mdb) > 0) {
264 ppathid_cache.insert(pathid);
265 /* This dir was in the db ...
266 * It means we can leave, the tree has allready been built for
271 /* search or create parent PathId in Path table */
272 mdb->path = bvfs_parent_dir(path);
273 mdb->pnl = strlen(mdb->path);
274 if (!db_create_path_record(jcr, mdb, &parent)) {
277 ppathid_cache.insert(pathid);
280 "INSERT INTO PathHierarchy (PathId, PPathId) "
282 pathid, (uint64_t) parent.PathId);
284 INSERT_DB(jcr, mdb, mdb->cmd);
286 edit_uint64(parent.PathId, pathid);
287 path = mdb->path; /* already done */
290 /* It's already in the cache. We can leave, no time to waste here,
291 * all the parent dirs have allready been done
303 * Internal function to update path_hierarchy cache with a shared pathid cache
305 static void update_path_hierarchy_cache(JCR *jcr,
307 pathid_cache &ppathid_cache,
310 Dmsg0(dbglevel, "update_path_hierarchy_cache()\n");
314 edit_uint64(JobId, jobid);
317 db_start_transaction(jcr, mdb);
319 Mmsg(mdb->cmd, "SELECT 1 FROM Job WHERE JobId = %s AND HasCache=1", jobid);
321 if (!QUERY_DB(jcr, mdb, mdb->cmd) || sql_num_rows(mdb) > 0) {
322 Dmsg1(dbglevel, "already computed %d\n", (uint32_t)JobId );
326 /* Inserting path records for JobId */
327 Mmsg(mdb->cmd, "INSERT INTO PathVisibility (PathId, JobId) "
328 "SELECT DISTINCT PathId, JobId "
329 "FROM (SELECT PathId, JobId FROM File WHERE JobId = %s "
331 "SELECT PathId, BaseFiles.JobId "
332 "FROM BaseFiles JOIN File AS F USING (FileId) "
333 "WHERE BaseFiles.JobId = %s) AS B",
335 QUERY_DB(jcr, mdb, mdb->cmd);
337 /* Now we have to do the directory recursion stuff to determine missing
338 * visibility We try to avoid recursion, to be as fast as possible We also
339 * only work on not allready hierarchised directories...
342 "SELECT PathVisibility.PathId, Path "
343 "FROM PathVisibility "
344 "JOIN Path ON( PathVisibility.PathId = Path.PathId) "
345 "LEFT JOIN PathHierarchy "
346 "ON (PathVisibility.PathId = PathHierarchy.PathId) "
347 "WHERE PathVisibility.JobId = %s "
348 "AND PathHierarchy.PathId IS NULL "
349 "ORDER BY Path", jobid);
350 Dmsg1(dbglevel_sql, "q=%s\n", mdb->cmd);
351 QUERY_DB(jcr, mdb, mdb->cmd);
353 /* TODO: I need to reuse the DB connection without emptying the result
354 * So, now i'm copying the result in memory to be able to query the
355 * catalog descriptor again.
357 num = sql_num_rows(mdb);
359 char **result = (char **)malloc (num * 2 * sizeof(char *));
363 while((row = sql_fetch_row(mdb))) {
364 result[i++] = bstrdup(row[0]);
365 result[i++] = bstrdup(row[1]);
370 build_path_hierarchy(jcr, mdb, ppathid_cache, result[i], result[i+1]);
379 "INSERT INTO PathVisibility (PathId, JobId) "
380 "SELECT a.PathId,%s "
382 "SELECT DISTINCT h.PPathId AS PathId "
383 "FROM PathHierarchy AS h "
384 "JOIN PathVisibility AS p ON (h.PathId=p.PathId) "
385 "WHERE p.JobId=%s) AS a LEFT JOIN "
387 "FROM PathVisibility "
388 "WHERE JobId=%s) AS b ON (a.PathId = b.PathId) "
389 "WHERE b.PathId IS NULL", jobid, jobid, jobid);
392 QUERY_DB(jcr, mdb, mdb->cmd);
393 } while (sql_affected_rows(mdb) > 0);
395 Mmsg(mdb->cmd, "UPDATE Job SET HasCache=1 WHERE JobId=%s", jobid);
396 UPDATE_DB(jcr, mdb, mdb->cmd);
399 db_end_transaction(jcr, mdb);
404 * Find an store the filename descriptor for empty directories Filename.Name=''
406 DBId_t Bvfs::get_dir_filenameid()
409 if (dir_filenameid) {
410 return dir_filenameid;
413 Mmsg(q, "SELECT FilenameId FROM Filename WHERE Name = ''");
414 db_sql_query(db, q.c_str(), db_int_handler, &id);
416 return dir_filenameid;
419 void bvfs_update_cache(JCR *jcr, B_DB *mdb)
422 db_list_ctx jobids_list;
425 db_start_transaction(jcr, mdb);
428 /* TODO: Remove this code when updating make_bacula_table script */
429 Mmsg(mdb->cmd, "SELECT 1 FROM Job WHERE HasCache<>2 LIMIT 1");
430 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
431 Dmsg0(dbglevel, "Creating cache table\n");
432 Mmsg(mdb->cmd, "ALTER TABLE Job ADD HasCache int DEFAULT 0");
433 QUERY_DB(jcr, mdb, mdb->cmd);
436 "CREATE TABLE PathHierarchy ( "
437 "PathId integer NOT NULL, "
438 "PPathId integer NOT NULL, "
439 "CONSTRAINT pathhierarchy_pkey "
440 "PRIMARY KEY (PathId))");
441 QUERY_DB(jcr, mdb, mdb->cmd);
444 "CREATE INDEX pathhierarchy_ppathid "
445 "ON PathHierarchy (PPathId)");
446 QUERY_DB(jcr, mdb, mdb->cmd);
449 "CREATE TABLE PathVisibility ("
450 "PathId integer NOT NULL, "
451 "JobId integer NOT NULL, "
452 "Size int8 DEFAULT 0, "
453 "Files int4 DEFAULT 0, "
454 "CONSTRAINT pathvisibility_pkey "
455 "PRIMARY KEY (JobId, PathId))");
456 QUERY_DB(jcr, mdb, mdb->cmd);
459 "CREATE INDEX pathvisibility_jobid "
460 "ON PathVisibility (JobId)");
461 QUERY_DB(jcr, mdb, mdb->cmd);
467 "SELECT JobId from Job "
468 "WHERE HasCache = 0 "
469 "AND Type IN ('B') AND JobStatus IN ('T', 'f', 'A') "
472 db_sql_query(mdb, mdb->cmd, db_list_handler, &jobids_list);
474 bvfs_update_path_hierarchy_cache(jcr, mdb, jobids_list.list);
476 db_end_transaction(jcr, mdb);
477 db_start_transaction(jcr, mdb);
478 Dmsg0(dbglevel, "Cleaning pathvisibility\n");
480 "DELETE FROM PathVisibility "
482 "(SELECT 1 FROM Job WHERE JobId=PathVisibility.JobId)");
483 nb = DELETE_DB(jcr, mdb, mdb->cmd);
484 Dmsg1(dbglevel, "Affected row(s) = %d\n", nb);
486 db_end_transaction(jcr, mdb);
491 * Update the bvfs cache for given jobids (1,2,3,4)
494 bvfs_update_path_hierarchy_cache(JCR *jcr, B_DB *mdb, char *jobids)
496 pathid_cache ppathid_cache;
501 int stat = get_next_jobid_from_list(&p, &JobId);
508 Dmsg1(dbglevel, "Updating cache for %lld\n", (uint64_t)JobId);
509 update_path_hierarchy_cache(jcr, mdb, ppathid_cache, JobId);
514 * Update the bvfs cache for current jobids
516 void Bvfs::update_cache()
518 bvfs_update_path_hierarchy_cache(jcr, db, jobids);
521 /* Change the current directory, returns true if the path exists */
522 bool Bvfs::ch_dir(const char *path)
524 pm_strcpy(db->path, path);
525 db->pnl = strlen(db->path);
526 ch_dir(db_get_path_record(jcr, db));
531 * Get all file versions for a specified client
532 * TODO: Handle basejobs using different client
534 void Bvfs::get_all_file_versions(DBId_t pathid, DBId_t fnid, const char *client)
536 Dmsg3(dbglevel, "get_all_file_versions(%lld, %lld, %s)\n", (uint64_t)pathid,
537 (uint64_t)fnid, client);
538 char ed1[50], ed2[50];
541 Mmsg(q, " AND Job.Type IN ('C', 'B') ");
543 Mmsg(q, " AND Job.Type = 'B' ");
549 "SELECT 'V', File.PathId, File.FilenameId, File.Md5, "
551 "File.JobId, File.LStat, File.FileId, "
553 "Media.VolumeName, Media.InChanger "
554 "FROM File, Job, Client, JobMedia, Media "
555 "WHERE File.FilenameId = %s "
556 "AND File.PathId=%s "
557 "AND File.JobId = Job.JobId "
558 "AND Job.JobId = JobMedia.JobId "
559 "AND File.FileIndex >= JobMedia.FirstIndex "
560 "AND File.FileIndex <= JobMedia.LastIndex "
561 "AND JobMedia.MediaId = Media.MediaId "
562 "AND Job.ClientId = Client.ClientId "
563 "AND Client.Name = '%s' "
564 "%s ORDER BY FileId LIMIT %d OFFSET %d"
565 ,edit_uint64(fnid, ed1), edit_uint64(pathid, ed2), client, q.c_str(),
567 Dmsg1(dbglevel_sql, "q=%s\n", query.c_str());
568 db_sql_query(db, query.c_str(), list_entries, user_data);
571 DBId_t Bvfs::get_root()
574 return db_get_path_record(jcr, db);
577 static int path_handler(void *ctx, int fields, char **row)
579 Bvfs *fs = (Bvfs *) ctx;
580 return fs->_handle_path(ctx, fields, row);
583 int Bvfs::_handle_path(void *ctx, int fields, char **row)
585 if (bvfs_is_dir(row)) {
586 /* can have the same path 2 times */
587 if (strcmp(row[BVFS_Name], prev_dir)) {
588 pm_strcpy(prev_dir, row[BVFS_Name]);
589 return list_entries(user_data, fields, row);
596 * Retrieve . and .. information
598 void Bvfs::ls_special_dirs()
600 Dmsg1(dbglevel, "ls_special_dirs(%lld)\n", (uint64_t)pwd_id);
601 char ed1[50], ed2[50];
605 if (!dir_filenameid) {
606 get_dir_filenameid();
609 /* Will fetch directories */
614 "((SELECT PPathId AS PathId, '..' AS Path "
615 "FROM PathHierarchy "
616 "WHERE PathId = %s) "
618 "(SELECT %s AS PathId, '.' AS Path))",
619 edit_uint64(pwd_id, ed1), ed1);
622 Mmsg(query2,// 1 2 3 4 5 6
623 "SELECT 'D', tmp.PathId, 0, tmp.Path, JobId, LStat, FileId "
624 "FROM %s AS tmp LEFT JOIN ( " // get attributes if any
625 "SELECT File1.PathId AS PathId, File1.JobId AS JobId, "
626 "File1.LStat AS LStat, File1.FileId AS FileId FROM File AS File1 "
627 "WHERE File1.FilenameId = %s "
628 "AND File1.JobId IN (%s)) AS listfile1 "
629 "ON (tmp.PathId = listfile1.PathId) "
630 "ORDER BY tmp.Path, JobId DESC ",
631 query.c_str(), edit_uint64(dir_filenameid, ed2), jobids);
633 Dmsg1(dbglevel_sql, "q=%s\n", query2.c_str());
634 db_sql_query(db, query2.c_str(), path_handler, this);
637 /* Returns true if we have dirs to read */
640 Dmsg1(dbglevel, "ls_dirs(%lld)\n", (uint64_t)pwd_id);
641 char ed1[50], ed2[50];
649 int len = strlen(pattern);
650 query.check_size(len*2+1);
651 db_escape_string(jcr, db, query.c_str(), pattern, len);
652 Mmsg(filter, " AND Path2.Path %s '%s' ", SQL_MATCH, query.c_str());
655 if (!dir_filenameid) {
656 get_dir_filenameid();
659 /* the sql query displays same directory multiple time, take the first one */
662 /* Let's retrieve the list of the visible dirs in this dir ...
663 * First, I need the empty filenameid to locate efficiently
664 * the dirs in the file table
665 * my $dir_filenameid = $self->get_dir_filenameid();
667 /* Then we get all the dir entries from File ... */
670 "SELECT 'D', PathId, 0, Path, JobId, LStat, FileId FROM ( "
671 "SELECT Path1.PathId AS PathId, Path1.Path AS Path, "
672 "lower(Path1.Path) AS lpath, "
673 "listfile1.JobId AS JobId, listfile1.LStat AS LStat, "
674 "listfile1.FileId AS FileId "
676 "SELECT DISTINCT PathHierarchy1.PathId AS PathId "
677 "FROM PathHierarchy AS PathHierarchy1 "
678 "JOIN Path AS Path2 "
679 "ON (PathHierarchy1.PathId = Path2.PathId) "
680 "JOIN PathVisibility AS PathVisibility1 "
681 "ON (PathHierarchy1.PathId = PathVisibility1.PathId) "
682 "WHERE PathHierarchy1.PPathId = %s "
683 "AND PathVisibility1.jobid IN (%s) "
686 "JOIN Path AS Path1 ON (listpath1.PathId = Path1.PathId) "
688 "LEFT JOIN ( " /* get attributes if any */
689 "SELECT File1.PathId AS PathId, File1.JobId AS JobId, "
690 "File1.LStat AS LStat, File1.FileId AS FileId FROM File AS File1 "
691 "WHERE File1.FilenameId = %s "
692 "AND File1.JobId IN (%s)) AS listfile1 "
693 "ON (listpath1.PathId = listfile1.PathId) "
694 ") AS A ORDER BY 2,3 DESC LIMIT %d OFFSET %d",
695 edit_uint64(pwd_id, ed1),
698 edit_uint64(dir_filenameid, ed2),
702 Dmsg1(dbglevel_sql, "q=%s\n", query.c_str());
705 db_sql_query(db, query.c_str(), path_handler, this);
706 nb_record = db->num_rows;
709 return nb_record == limit;
712 void build_ls_files_query(B_DB *db, POOL_MEM &query,
713 const char *JobId, const char *PathId,
714 const char *filter, int64_t limit, int64_t offset)
716 if (db_type == SQL_TYPE_POSTGRESQL) {
717 Mmsg(query, sql_bvfs_list_files[db_type],
718 JobId, PathId, JobId, PathId,
719 filter, limit, offset);
721 Mmsg(query, sql_bvfs_list_files[db_type],
722 JobId, PathId, JobId, PathId,
723 limit, offset, filter, JobId, JobId);
727 /* Returns true if we have files to read */
728 bool Bvfs::ls_files()
734 Dmsg1(dbglevel, "ls_files(%lld)\n", (uint64_t)pwd_id);
743 edit_uint64(pwd_id, pathid);
745 int len = strlen(pattern);
746 query.check_size(len*2+1);
747 db_escape_string(jcr, db, query.c_str(), pattern, len);
748 Mmsg(filter, " AND Filename.Name %s '%s' ", SQL_MATCH, query.c_str());
751 build_ls_files_query(db, query,
752 jobids, pathid, filter.c_str(),
755 Dmsg1(dbglevel_sql, "q=%s\n", query.c_str());
758 db_sql_query(db, query.c_str(), list_entries, user_data);
759 nb_record = db->num_rows;
762 return nb_record == limit;
767 * Return next Id from comma separated list
770 * 1 if next Id returned
771 * 0 if no more Ids are in list
772 * -1 there is an error
773 * TODO: merge with get_next_jobid_from_list() and get_next_dbid_from_list()
775 static int get_next_id_from_list(char **p, int64_t *Id)
777 const int maxlen = 30;
782 for (int i=0; i<maxlen; i++) {
785 } else if (*q == ',') {
794 } else if (!is_a_number(id)) {
795 return -1; /* error */
798 *Id = str_to_int64(id);
802 static int get_path_handler(void *ctx, int fields, char **row)
804 POOL_MEM *buf = (POOL_MEM *) ctx;
805 pm_strcpy(*buf, row[0]);
809 static bool check_temp(char *output_table)
811 if (output_table[0] == 'b' &&
812 output_table[1] == '2' &&
813 is_an_integer(output_table + 2))
820 bool Bvfs::drop_restore_list(char *output_table)
823 if (check_temp(output_table)) {
824 Mmsg(query, "DROP TABLE %s", output_table);
825 db_sql_query(db, query.c_str(), NULL, NULL);
831 bool Bvfs::compute_restore_list(char *fileid, char *dirid, char *hardlink,
840 if ((*fileid && !is_a_number_list(fileid)) ||
841 (*dirid && !is_a_number_list(dirid)) ||
842 (*hardlink && !is_a_number_list(hardlink))||
843 (!*hardlink && !*fileid && !*dirid && !*hardlink))
847 if (!check_temp(output_table)) {
851 Mmsg(query, "CREATE TEMPORARY TABLE btemp%s AS ", output_table);
853 if (*fileid) { /* Select files with their direct id */
855 Mmsg(tmp,"(SELECT JobId, JobTDate, FileIndex, FilenameId, PathId, FileId "
856 "FROM File JOIN Job USING (JobId) WHERE FileId IN (%s))",
858 pm_strcat(query, tmp.c_str());
861 /* Add a directory content */
862 while (get_next_id_from_list(&dirid, &id) == 1) {
863 Mmsg(tmp, "SELECT Path FROM Path WHERE PathId=%lld", id);
865 if (!db_sql_query(db, tmp.c_str(), get_path_handler, (void *)&tmp2)) {
866 Dmsg0(dbglevel, "Can't search for path\n");
870 if (!strcmp(tmp2.c_str(), "")) { /* path not found */
871 Dmsg3(dbglevel, "Path not found %lld q=%s s=%s\n",
872 id, tmp.c_str(), tmp2.c_str());
875 /* escape % and _ for LIKE search */
876 tmp.check_size((strlen(tmp2.c_str())+1) * 2);
877 char *p = tmp.c_str();
878 for (char *s = tmp2.c_str(); *s ; s++) {
879 if (*s == '%' || *s == '_' || *s == '\\') {
889 size_t len = strlen(tmp.c_str());
890 tmp2.check_size((len+1) * 2);
891 db_escape_string(jcr, db, tmp2.c_str(), tmp.c_str(), len);
894 query.strcat(" UNION ");
897 Mmsg(tmp, "(SELECT JobId, JobTDate, File.FileIndex, File.FilenameId, "
898 "File.PathId, FileId "
899 "FROM Path JOIN File USING (PathId) JOIN Job USING (JobId) "
900 "WHERE Path.Path LIKE '%s' AND File.JobId IN (%s)) ",
901 tmp2.c_str(), jobids);
902 query.strcat(tmp.c_str());
905 query.strcat(" UNION ");
907 /* A directory can have files from a BaseJob */
908 Mmsg(tmp, "(SELECT File.JobId, JobTDate, BaseFiles.FileIndex, "
909 "File.FilenameId, File.PathId, BaseFiles.FileId "
911 "JOIN File USING (FileId) "
912 "JOIN Job ON (BaseFiles.JobId = Job.JobId) "
913 "JOIN Path USING (PathId) "
914 "WHERE Path.Path LIKE '%s' AND BaseFiles.JobId IN (%s)) ",
915 tmp2.c_str(), jobids);
916 query.strcat(tmp.c_str());
919 /* expect jobid,fileindex */
920 int64_t prev_jobid=0;
921 while (get_next_id_from_list(&hardlink, &jobid) == 1) {
922 if (get_next_id_from_list(&hardlink, &id) != 1) {
923 Dmsg0(dbglevel, "hardlink should be two by two\n");
926 if (jobid != prev_jobid) { /* new job */
927 if (prev_jobid == 0) { /* first jobid */
929 query.strcat(" UNION ");
931 } else { /* end last job, start new one */
932 tmp.strcat(")) UNION ");
933 query.strcat(tmp.c_str());
935 Mmsg(tmp, "(SELECT JobId, JobTDate, FileIndex, FilenameId, "
937 "FROM File JOIN Job USING (JobId) WHERE JobId = %lld "
938 "AND FileIndex IN (%lld", jobid, id);
941 } else { /* same job, add new findex */
942 Mmsg(tmp2, ", %lld", id);
943 tmp.strcat(tmp2.c_str());
947 if (prev_jobid != 0) { /* end last job */
949 query.strcat(tmp.c_str());
953 Dmsg1(dbglevel_sql, "q=%s\n", query.c_str());
955 if (!db_sql_query(db, query.c_str(), NULL, NULL)) {
956 Dmsg0(dbglevel, "Can't execute q\n");
960 /* TODO: handle basejob and SQLite3 */
961 Mmsg(query, sql_bvfs_select[db_type], output_table, output_table);
963 /* TODO: handle jobid filter */
964 Dmsg1(dbglevel_sql, "q=%s\n", query.c_str());
965 if (!db_sql_query(db, query.c_str(), NULL, NULL)) {
966 Dmsg0(dbglevel, "Can't execute q\n");
971 if (db_type == SQL_TYPE_MYSQL) {
972 Mmsg(query, "CREATE INDEX idx_%s ON b2%s (JobId)",
973 output_table, output_table);
979 Mmsg(query, "DROP TABLE btemp%s", output_table);
980 db_sql_query(db, query.c_str(), NULL, NULL);