2 Bacula® - The Network Backup Solution
4 Copyright (C) 2009-2014 Free Software Foundation Europe e.V.
6 The main author of Bacula is Kern Sibbald, with contributions from many
7 others, a complete list can be found in the file AUTHORS.
9 You may use this file and others of this release according to the
10 license defined in the LICENSE file, which includes the Affero General
11 Public License, v3.0 ("AGPLv3") and some additional permissions and
12 terms pursuant to its AGPLv3 Section 7.
14 Bacula® is a registered trademark of Kern Sibbald.
19 #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL
24 #include "lib/htable.h"
28 #define dbglevel_sql 15
30 static int result_handler(void *ctx, int fields, char **row)
33 Pmsg4(0, "%s\t%s\t%s\t%s\n",
34 row[0], row[1], row[2], row[3]);
35 } else if (fields == 5) {
36 Pmsg5(0, "%s\t%s\t%s\t%s\t%s\n",
37 row[0], row[1], row[2], row[3], row[4]);
38 } else if (fields == 6) {
39 Pmsg6(0, "%s\t%s\t%s\t%s\t%s\t%s\n",
40 row[0], row[1], row[2], row[3], row[4], row[5]);
41 } else if (fields == 7) {
42 Pmsg7(0, "%s\t%s\t%s\t%s\t%s\t%s\t%s\n",
43 row[0], row[1], row[2], row[3], row[4], row[5], row[6]);
48 Bvfs::Bvfs(JCR *j, B_DB *mdb) {
51 db = mdb; /* need to inc ref count */
52 jobids = get_pool_memory(PM_NAME);
53 prev_dir = get_pool_memory(PM_NAME);
54 pattern = get_pool_memory(PM_NAME);
55 filename = get_pool_memory(PM_NAME);
56 tmp = get_pool_memory(PM_NAME);
57 escaped_list = get_pool_memory(PM_NAME);
58 *filename = *jobids = *prev_dir = *pattern = 0;
59 dir_filenameid = pwd_id = offset = 0;
60 see_copies = see_all_versions = false;
63 list_entries = result_handler;
66 job_acl = client_acl = pool_acl = fileset_acl = NULL;
70 free_pool_memory(jobids);
71 free_pool_memory(pattern);
72 free_pool_memory(prev_dir);
73 free_pool_memory(filename);
74 free_pool_memory(tmp);
75 free_pool_memory(escaped_list);
83 char *Bvfs::escape_list(alist *lst)
88 /* List is empty, reject everything */
89 if (!lst || lst->size() == 0) {
90 Mmsg(escaped_list, "''");
97 foreach_alist(elt, lst) {
101 tmp = check_pool_memory_size(tmp, 2 * len + 2 + 2);
104 db_escape_string(jcr, db, tmp + 1 , elt, len);
108 pm_strcat(escaped_list, ",");
111 pm_strcat(escaped_list, tmp);
117 void Bvfs::filter_jobid()
123 /* No ACL, no username, no check */
124 if (!job_acl && !fileset_acl && !client_acl && !pool_acl && !username) {
125 Dmsg0(dbglevel_sql, "No ACL\n");
130 Mmsg(sub_where, " AND Job.Name IN (%s) ", escape_list(job_acl));
134 Mmsg(query, " AND FileSet.FileSet IN (%s) ", escape_list(fileset_acl));
135 pm_strcat(sub_where, query.c_str());
136 pm_strcat(sub_join, " JOIN FileSet USING (FileSetId) ");
140 Mmsg(query, " AND Client.Name IN (%s) ", escape_list(client_acl));
141 pm_strcat(sub_where, query.c_str());
145 Mmsg(query, " AND Pool.Name IN (%s) ", escape_list(pool_acl));
146 pm_strcat(sub_where, query.c_str());
147 pm_strcat(sub_join, " JOIN Pool USING (PoolId) ");
151 /* Query used by Bweb to filter clients, activated when using
155 "SELECT DISTINCT JobId FROM Job JOIN Client USING (ClientId) %s "
156 "JOIN (SELECT ClientId FROM client_group_member "
157 "JOIN client_group USING (client_group_id) "
158 "JOIN bweb_client_group_acl USING (client_group_id) "
159 "JOIN bweb_user USING (userid) "
160 "WHERE bweb_user.username = '%s' "
161 ") AS filter USING (ClientId) "
162 " WHERE JobId IN (%s) %s",
163 sub_join.c_str(), username, jobids, sub_where.c_str());
167 "SELECT DISTINCT JobId FROM Job JOIN Client USING (ClientId) %s "
168 " WHERE JobId IN (%s) %s",
169 sub_join.c_str(), jobids, sub_where.c_str());
173 Dmsg1(dbglevel_sql, "q=%s\n", query.c_str());
174 db_sql_query(db, query.c_str(), db_list_handler, &ctx);
175 pm_strcpy(jobids, ctx.list);
178 void Bvfs::set_jobid(JobId_t id)
180 Mmsg(jobids, "%lld", (uint64_t)id);
184 void Bvfs::set_jobids(char *ids)
186 pm_strcpy(jobids, ids);
191 * TODO: Find a way to let the user choose how he wants to display
192 * files and directories
197 * Working Object to store PathId already seen (avoid
198 * database queries), equivalent to %cache_ppathid in perl
210 htable *cache_ppathid;
215 cache_ppathid = (htable *)malloc(sizeof(htable));
216 cache_ppathid->init(&link, &link, NITEMS);
218 nodes = (hlink *) malloc(max_node * sizeof (hlink));
220 table_node = New(alist(5, owned_by_alist));
221 table_node->append(nodes);
225 if (++nb_node >= max_node) {
227 nodes = (hlink *)malloc(max_node * sizeof(hlink));
228 table_node->append(nodes);
230 return nodes + nb_node;
233 bool lookup(char *pathid) {
234 bool ret = cache_ppathid->lookup(pathid) != NULL;
238 void insert(char *pathid) {
239 hlink *h = get_hlink();
240 cache_ppathid->insert(pathid, h);
244 cache_ppathid->destroy();
249 pathid_cache(const pathid_cache &); /* prohibit pass by value */
250 pathid_cache &operator= (const pathid_cache &);/* prohibit class assignment*/
253 /* Return the parent_dir with the trailing / (update the given string)
254 * TODO: see in the rest of bacula if we don't have already this function
260 char *bvfs_parent_dir(char *path)
263 int len = strlen(path) - 1;
265 /* windows directory / */
266 if (len == 2 && B_ISALPHA(path[0])
274 if (len >= 0 && path[len] == '/') { /* if directory, skip last / */
280 while (p > path && !IsPathSeparator(*p)) {
288 /* Return the basename of the with the trailing /
289 * TODO: see in the rest of bacula if we don't have
290 * this function already
292 char *bvfs_basename_dir(char *path)
295 int len = strlen(path) - 1;
297 if (path[len] == '/') { /* if directory, skip last / */
303 while (p > path && !IsPathSeparator(*p)) {
307 p++; /* skip first / */
313 static void build_path_hierarchy(JCR *jcr, B_DB *mdb,
314 pathid_cache &ppathid_cache,
315 char *org_pathid, char *path)
317 Dmsg1(dbglevel, "build_path_hierarchy(%s)\n", path);
320 char *bkp = mdb->path;
321 strncpy(pathid, org_pathid, sizeof(pathid));
323 /* Does the ppathid exist for this ? we use a memory cache... In order to
324 * avoid the full loop, we consider that if a dir is allready in the
325 * PathHierarchy table, then there is no need to calculate all the
328 while (path && *path)
330 if (!ppathid_cache.lookup(pathid))
333 "SELECT PPathId FROM PathHierarchy WHERE PathId = %s",
336 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
337 goto bail_out; /* Query failed, just leave */
340 /* Do we have a result ? */
341 if (sql_num_rows(mdb) > 0) {
342 ppathid_cache.insert(pathid);
343 /* This dir was in the db ...
344 * It means we can leave, the tree has allready been built for
349 /* search or create parent PathId in Path table */
350 mdb->path = bvfs_parent_dir(path);
351 mdb->pnl = strlen(mdb->path);
352 if (!db_create_path_record(jcr, mdb, &parent)) {
355 ppathid_cache.insert(pathid);
358 "INSERT INTO PathHierarchy (PathId, PPathId) "
360 pathid, (uint64_t) parent.PathId);
362 if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
363 goto bail_out; /* Can't insert the record, just leave */
366 edit_uint64(parent.PathId, pathid);
367 path = mdb->path; /* already done */
370 /* It's already in the cache. We can leave, no time to waste here,
371 * all the parent dirs have allready been done
383 * Internal function to update path_hierarchy cache with a shared pathid cache
387 static int update_path_hierarchy_cache(JCR *jcr,
389 pathid_cache &ppathid_cache,
392 Dmsg0(dbglevel, "update_path_hierarchy_cache()\n");
396 edit_uint64(JobId, jobid);
399 db_start_transaction(jcr, mdb);
401 Mmsg(mdb->cmd, "SELECT 1 FROM Job WHERE JobId = %s AND HasCache=1", jobid);
403 if (!QUERY_DB(jcr, mdb, mdb->cmd) || sql_num_rows(mdb) > 0) {
404 Dmsg1(dbglevel, "already computed %d\n", (uint32_t)JobId );
409 /* Inserting path records for JobId */
410 Mmsg(mdb->cmd, "INSERT INTO PathVisibility (PathId, JobId) "
411 "SELECT DISTINCT PathId, JobId "
412 "FROM (SELECT PathId, JobId FROM File WHERE JobId = %s "
414 "SELECT PathId, BaseFiles.JobId "
415 "FROM BaseFiles JOIN File AS F USING (FileId) "
416 "WHERE BaseFiles.JobId = %s) AS B",
419 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
420 Dmsg1(dbglevel, "Can't fill PathVisibility %d\n", (uint32_t)JobId );
424 /* Now we have to do the directory recursion stuff to determine missing
425 * visibility We try to avoid recursion, to be as fast as possible We also
426 * only work on not allready hierarchised directories...
429 "SELECT PathVisibility.PathId, Path "
430 "FROM PathVisibility "
431 "JOIN Path ON( PathVisibility.PathId = Path.PathId) "
432 "LEFT JOIN PathHierarchy "
433 "ON (PathVisibility.PathId = PathHierarchy.PathId) "
434 "WHERE PathVisibility.JobId = %s "
435 "AND PathHierarchy.PathId IS NULL "
436 "ORDER BY Path", jobid);
437 Dmsg1(dbglevel_sql, "q=%s\n", mdb->cmd);
439 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
440 Dmsg1(dbglevel, "Can't get new Path %d\n", (uint32_t)JobId );
444 /* TODO: I need to reuse the DB connection without emptying the result
445 * So, now i'm copying the result in memory to be able to query the
446 * catalog descriptor again.
448 num = sql_num_rows(mdb);
450 char **result = (char **)malloc (num * 2 * sizeof(char *));
454 while((row = sql_fetch_row(mdb))) {
455 result[i++] = bstrdup(row[0]);
456 result[i++] = bstrdup(row[1]);
461 build_path_hierarchy(jcr, mdb, ppathid_cache, result[i], result[i+1]);
469 if (mdb->db_get_type_index() == SQL_TYPE_SQLITE3) {
471 "INSERT INTO PathVisibility (PathId, JobId) "
472 "SELECT DISTINCT h.PPathId AS PathId, %s "
473 "FROM PathHierarchy AS h "
474 "WHERE h.PathId IN (SELECT PathId FROM PathVisibility WHERE JobId=%s) "
475 "AND h.PPathId NOT IN (SELECT PathId FROM PathVisibility WHERE JobId=%s)",
476 jobid, jobid, jobid );
480 "INSERT INTO PathVisibility (PathId, JobId) "
481 "SELECT a.PathId,%s "
483 "SELECT DISTINCT h.PPathId AS PathId "
484 "FROM PathHierarchy AS h "
485 "JOIN PathVisibility AS p ON (h.PathId=p.PathId) "
486 "WHERE p.JobId=%s) AS a LEFT JOIN "
488 "FROM PathVisibility "
489 "WHERE JobId=%s) AS b ON (a.PathId = b.PathId) "
490 "WHERE b.PathId IS NULL", jobid, jobid, jobid);
494 ret = QUERY_DB(jcr, mdb, mdb->cmd);
495 } while (ret && sql_affected_rows(mdb) > 0);
497 Mmsg(mdb->cmd, "UPDATE Job SET HasCache=1 WHERE JobId=%s", jobid);
498 UPDATE_DB(jcr, mdb, mdb->cmd);
501 db_end_transaction(jcr, mdb);
507 * Find an store the filename descriptor for empty directories Filename.Name=''
509 DBId_t Bvfs::get_dir_filenameid()
512 if (dir_filenameid) {
513 return dir_filenameid;
515 Mmsg(db->cmd, "SELECT FilenameId FROM Filename WHERE Name = ''");
516 db_sql_query(db, db->cmd, db_int_handler, &id);
518 return dir_filenameid;
521 /* Compute the cache for the bfileview compoment */
522 void Bvfs::fv_update_cache()
525 int64_t size=0, count=0;
527 Dmsg0(dbglevel, "fv_update_cache()\n");
530 return; /* Nothing to build */
534 db_start_transaction(jcr, db);
538 fv_compute_size_and_count(pathid, &size, &count);
540 db_end_transaction(jcr, db);
544 /* Not yet working */
545 void Bvfs::fv_get_big_files(int64_t pathid, int64_t min_size, int32_t limit)
548 "SELECT FilenameId AS filenameid, Name AS name, size "
550 "SELECT FilenameId, base64_decode_lstat(8,LStat) AS size "
552 "WHERE PathId = %lld "
554 ") AS S INNER JOIN Filename USING (FilenameId) "
555 "WHERE S.size > %lld "
556 "ORDER BY S.size DESC "
557 "LIMIT %d ", pathid, jobids, min_size, limit);
560 /* Get the current path size and files count */
561 void Bvfs::fv_get_current_size_and_count(int64_t pathid, int64_t *size, int64_t *count)
568 "SELECT Size AS size, Files AS files "
569 " FROM PathVisibility "
570 " WHERE PathId = %lld "
571 " AND JobId = %s ", pathid, jobids);
573 if (!QUERY_DB(jcr, db, db->cmd)) {
577 if ((row = sql_fetch_row(db))) {
578 *size = str_to_int64(row[0]);
579 *count = str_to_int64(row[1]);
583 /* Compute for the current path the size and files count */
584 void Bvfs::fv_get_size_and_count(int64_t pathid, int64_t *size, int64_t *count)
591 "SELECT sum(base64_decode_lstat(8,LStat)) AS size, count(1) AS files "
593 " WHERE PathId = %lld "
594 " AND JobId = %s ", pathid, jobids);
596 if (!QUERY_DB(jcr, db, db->cmd)) {
600 if ((row = sql_fetch_row(db))) {
601 *size = str_to_int64(row[0]);
602 *count = str_to_int64(row[1]);
606 void Bvfs::fv_compute_size_and_count(int64_t pathid, int64_t *size, int64_t *count)
608 Dmsg1(dbglevel, "fv_compute_size_and_count(%lld)\n", pathid);
610 fv_get_current_size_and_count(pathid, size, count);
615 /* Update stats for the current directory */
616 fv_get_size_and_count(pathid, size, count);
618 /* Update stats for all sub directories */
621 " FROM PathVisibility "
622 " INNER JOIN PathHierarchy USING (PathId) "
623 " WHERE PPathId = %lld "
624 " AND JobId = %s ", pathid, jobids);
626 QUERY_DB(jcr, db, db->cmd);
627 int num = sql_num_rows(db);
630 int64_t *result = (int64_t *)malloc (num * sizeof(int64_t));
634 while((row = sql_fetch_row(db))) {
635 result[i++] = str_to_int64(row[0]); /* PathId */
641 fv_compute_size_and_count(result[i], &s, &c);
651 fv_update_size_and_count(pathid, *size, *count);
654 void Bvfs::fv_update_size_and_count(int64_t pathid, int64_t size, int64_t count)
657 "UPDATE PathVisibility SET Files = %lld, Size = %lld "
659 " AND PathId = %lld ", count, size, jobids, pathid);
661 UPDATE_DB(jcr, db, db->cmd);
664 void bvfs_update_cache(JCR *jcr, B_DB *mdb)
667 db_list_ctx jobids_list;
672 /* TODO: Remove this code when updating make_bacula_table script */
673 Mmsg(mdb->cmd, "SELECT 1 FROM Job WHERE HasCache<>2 LIMIT 1");
674 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
675 Dmsg0(dbglevel, "Creating cache table\n");
676 Mmsg(mdb->cmd, "ALTER TABLE Job ADD HasCache int DEFAULT 0");
677 QUERY_DB(jcr, mdb, mdb->cmd);
680 "CREATE TABLE PathHierarchy ( "
681 "PathId integer NOT NULL, "
682 "PPathId integer NOT NULL, "
683 "CONSTRAINT pathhierarchy_pkey "
684 "PRIMARY KEY (PathId))");
685 QUERY_DB(jcr, mdb, mdb->cmd);
688 "CREATE INDEX pathhierarchy_ppathid "
689 "ON PathHierarchy (PPathId)");
690 QUERY_DB(jcr, mdb, mdb->cmd);
693 "CREATE TABLE PathVisibility ("
694 "PathId integer NOT NULL, "
695 "JobId integer NOT NULL, "
696 "Size int8 DEFAULT 0, "
697 "Files int4 DEFAULT 0, "
698 "CONSTRAINT pathvisibility_pkey "
699 "PRIMARY KEY (JobId, PathId))");
700 QUERY_DB(jcr, mdb, mdb->cmd);
703 "CREATE INDEX pathvisibility_jobid "
704 "ON PathVisibility (JobId)");
705 QUERY_DB(jcr, mdb, mdb->cmd);
711 "SELECT JobId from Job "
712 "WHERE HasCache = 0 "
713 "AND Type IN ('B') AND JobStatus IN ('T', 'f', 'A') "
716 db_sql_query(mdb, mdb->cmd, db_list_handler, &jobids_list);
718 bvfs_update_path_hierarchy_cache(jcr, mdb, jobids_list.list);
720 db_start_transaction(jcr, mdb);
721 Dmsg0(dbglevel, "Cleaning pathvisibility\n");
723 "DELETE FROM PathVisibility "
725 "(SELECT 1 FROM Job WHERE JobId=PathVisibility.JobId)");
726 nb = DELETE_DB(jcr, mdb, mdb->cmd);
727 Dmsg1(dbglevel, "Affected row(s) = %d\n", nb);
729 db_end_transaction(jcr, mdb);
734 * Update the bvfs cache for given jobids (1,2,3,4)
737 bvfs_update_path_hierarchy_cache(JCR *jcr, B_DB *mdb, char *jobids)
739 pathid_cache ppathid_cache;
745 int stat = get_next_jobid_from_list(&p, &JobId);
752 Dmsg1(dbglevel, "Updating cache for %lld\n", (uint64_t)JobId);
753 if (!update_path_hierarchy_cache(jcr, mdb, ppathid_cache, JobId)) {
761 * Update the bvfs fileview for given jobids
764 bvfs_update_fv_cache(JCR *jcr, B_DB *mdb, char *jobids)
771 int stat = get_next_jobid_from_list(&p, &JobId);
779 Dmsg1(dbglevel, "Trying to create cache for %lld\n", (int64_t)JobId);
781 bvfs.set_jobid(JobId);
782 bvfs.fv_update_cache();
787 * Update the bvfs cache for current jobids
789 void Bvfs::update_cache()
791 bvfs_update_path_hierarchy_cache(jcr, db, jobids);
794 /* Change the current directory, returns true if the path exists */
795 bool Bvfs::ch_dir(const char *path)
797 pm_strcpy(db->path, path);
798 db->pnl = strlen(db->path);
800 ch_dir(db_get_path_record(jcr, db));
806 * Get all file versions for a specified client
807 * TODO: Handle basejobs using different client
809 void Bvfs::get_all_file_versions(DBId_t pathid, DBId_t fnid, const char *client)
811 Dmsg3(dbglevel, "get_all_file_versions(%lld, %lld, %s)\n", (uint64_t)pathid,
812 (uint64_t)fnid, client);
813 char ed1[50], ed2[50];
816 Mmsg(q, " AND Job.Type IN ('C', 'B') ");
818 Mmsg(q, " AND Job.Type = 'B' ");
824 "SELECT 'V', File.PathId, File.FilenameId, File.Md5, "
826 "File.JobId, File.LStat, File.FileId, "
828 "Media.VolumeName, Media.InChanger "
829 "FROM File, Job, Client, JobMedia, Media "
830 "WHERE File.FilenameId = %s "
831 "AND File.PathId=%s "
832 "AND File.JobId = Job.JobId "
833 "AND Job.JobId = JobMedia.JobId "
834 "AND File.FileIndex >= JobMedia.FirstIndex "
835 "AND File.FileIndex <= JobMedia.LastIndex "
836 "AND JobMedia.MediaId = Media.MediaId "
837 "AND Job.ClientId = Client.ClientId "
838 "AND Client.Name = '%s' "
839 "%s ORDER BY FileId LIMIT %d OFFSET %d"
840 ,edit_uint64(fnid, ed1), edit_uint64(pathid, ed2), client, q.c_str(),
842 Dmsg1(dbglevel_sql, "q=%s\n", query.c_str());
843 db_sql_query(db, query.c_str(), list_entries, user_data);
847 * Get all volumes for a specific file
849 void Bvfs::get_volumes(DBId_t fileid)
851 Dmsg1(dbglevel, "get_volumes(%lld)\n", (uint64_t)fileid);
858 "SELECT 'L',0,0,0,0,0,0, Media.VolumeName, Media.InChanger "
859 "FROM File JOIN JobMedia USING (JobId) JOIN Media USING (MediaId) "
860 "WHERE File.FileId = %s "
861 "AND File.FileIndex >= JobMedia.FirstIndex "
862 "AND File.FileIndex <= JobMedia.LastIndex "
863 " ORDER BY JobMediaId LIMIT %d OFFSET %d"
864 ,edit_uint64(fileid, ed1), limit, offset);
865 Dmsg1(dbglevel_sql, "q=%s\n", query.c_str());
866 db_sql_query(db, query.c_str(), list_entries, user_data);
869 DBId_t Bvfs::get_root()
874 p = db_get_path_record(jcr, db);
879 static int path_handler(void *ctx, int fields, char **row)
881 Bvfs *fs = (Bvfs *) ctx;
882 return fs->_handle_path(ctx, fields, row);
885 int Bvfs::_handle_path(void *ctx, int fields, char **row)
887 if (bvfs_is_dir(row)) {
888 /* can have the same path 2 times */
889 if (strcmp(row[BVFS_PathId], prev_dir)) {
890 pm_strcpy(prev_dir, row[BVFS_PathId]);
891 return list_entries(user_data, fields, row);
898 * Retrieve . and .. information
900 void Bvfs::ls_special_dirs()
902 Dmsg1(dbglevel, "ls_special_dirs(%lld)\n", (uint64_t)pwd_id);
903 char ed1[50], ed2[50];
907 if (!dir_filenameid) {
908 get_dir_filenameid();
911 /* Will fetch directories */
916 "(SELECT PPathId AS PathId, '..' AS Path "
917 "FROM PathHierarchy "
920 "SELECT %s AS PathId, '.' AS Path)",
921 edit_uint64(pwd_id, ed1), ed1);
924 Mmsg(query2,// 1 2 3 4 5 6
925 "SELECT 'D', tmp.PathId, 0, tmp.Path, JobId, LStat, FileId "
926 "FROM %s AS tmp LEFT JOIN ( " // get attributes if any
927 "SELECT File1.PathId AS PathId, File1.JobId AS JobId, "
928 "File1.LStat AS LStat, File1.FileId AS FileId FROM File AS File1 "
929 "WHERE File1.FilenameId = %s "
930 "AND File1.JobId IN (%s)) AS listfile1 "
931 "ON (tmp.PathId = listfile1.PathId) "
932 "ORDER BY tmp.Path, JobId DESC ",
933 query.c_str(), edit_uint64(dir_filenameid, ed2), jobids);
935 Dmsg1(dbglevel_sql, "q=%s\n", query2.c_str());
936 db_sql_query(db, query2.c_str(), path_handler, this);
939 /* Returns true if we have dirs to read */
942 Dmsg1(dbglevel, "ls_dirs(%lld)\n", (uint64_t)pwd_id);
943 char ed1[50], ed2[50];
951 Mmsg(filter, " AND Path2.Path %s '%s' ",
952 match_query[db_get_type_index(db)], pattern);
956 if (!dir_filenameid) {
957 get_dir_filenameid();
960 /* the sql query displays same directory multiple time, take the first one */
963 /* Let's retrieve the list of the visible dirs in this dir ...
964 * First, I need the empty filenameid to locate efficiently
965 * the dirs in the file table
966 * my $dir_filenameid = $self->get_dir_filenameid();
968 /* Then we get all the dir entries from File ... */
971 "SELECT 'D', PathId, 0, Path, JobId, LStat, FileId FROM ( "
972 "SELECT Path1.PathId AS PathId, Path1.Path AS Path, "
973 "lower(Path1.Path) AS lpath, "
974 "listfile1.JobId AS JobId, listfile1.LStat AS LStat, "
975 "listfile1.FileId AS FileId "
977 "SELECT DISTINCT PathHierarchy1.PathId AS PathId "
978 "FROM PathHierarchy AS PathHierarchy1 "
979 "JOIN Path AS Path2 "
980 "ON (PathHierarchy1.PathId = Path2.PathId) "
981 "JOIN PathVisibility AS PathVisibility1 "
982 "ON (PathHierarchy1.PathId = PathVisibility1.PathId) "
983 "WHERE PathHierarchy1.PPathId = %s "
984 "AND PathVisibility1.JobId IN (%s) "
987 "JOIN Path AS Path1 ON (listpath1.PathId = Path1.PathId) "
989 "LEFT JOIN ( " /* get attributes if any */
990 "SELECT File1.PathId AS PathId, File1.JobId AS JobId, "
991 "File1.LStat AS LStat, File1.FileId AS FileId FROM File AS File1 "
992 "WHERE File1.FilenameId = %s "
993 "AND File1.JobId IN (%s)) AS listfile1 "
994 "ON (listpath1.PathId = listfile1.PathId) "
995 ") AS A ORDER BY 2,3 DESC LIMIT %d OFFSET %d",
996 edit_uint64(pwd_id, ed1),
999 edit_uint64(dir_filenameid, ed2),
1003 Dmsg1(dbglevel_sql, "q=%s\n", query.c_str());
1006 db_sql_query(db, query.c_str(), path_handler, this);
1007 nb_record = sql_num_rows(db);
1010 return nb_record == limit;
1013 void build_ls_files_query(B_DB *db, POOL_MEM &query,
1014 const char *JobId, const char *PathId,
1015 const char *filter, int64_t limit, int64_t offset)
1017 if (db_get_type_index(db) == SQL_TYPE_POSTGRESQL) {
1018 Mmsg(query, sql_bvfs_list_files[db_get_type_index(db)],
1019 JobId, PathId, JobId, PathId,
1020 filter, limit, offset);
1022 Mmsg(query, sql_bvfs_list_files[db_get_type_index(db)],
1023 JobId, PathId, JobId, PathId,
1024 limit, offset, filter, JobId, JobId);
1028 /* Returns true if we have files to read */
1029 bool Bvfs::ls_files()
1035 Dmsg1(dbglevel, "ls_files(%lld)\n", (uint64_t)pwd_id);
1044 edit_uint64(pwd_id, pathid);
1046 Mmsg(filter, " AND Filename.Name %s '%s' ",
1047 match_query[db_get_type_index(db)], pattern);
1049 } else if (*filename) {
1050 Mmsg(filter, " AND Filename.Name = '%s' ", filename);
1053 build_ls_files_query(db, query,
1054 jobids, pathid, filter.c_str(),
1057 Dmsg1(dbglevel_sql, "q=%s\n", query.c_str());
1060 db_sql_query(db, query.c_str(), list_entries, user_data);
1061 nb_record = sql_num_rows(db);
1064 return nb_record == limit;
1069 * Return next Id from comma separated list
1072 * 1 if next Id returned
1073 * 0 if no more Ids are in list
1074 * -1 there is an error
1075 * TODO: merge with get_next_jobid_from_list() and get_next_dbid_from_list()
1077 static int get_next_id_from_list(char **p, int64_t *Id)
1079 const int maxlen = 30;
1084 for (int i=0; i<maxlen; i++) {
1087 } else if (*q == ',') {
1096 } else if (!is_a_number(id)) {
1097 return -1; /* error */
1100 *Id = str_to_int64(id);
1104 static int get_path_handler(void *ctx, int fields, char **row)
1106 POOL_MEM *buf = (POOL_MEM *) ctx;
1107 pm_strcpy(*buf, row[0]);
1111 static bool check_temp(char *output_table)
1113 if (output_table[0] == 'b' &&
1114 output_table[1] == '2' &&
1115 is_an_integer(output_table + 2))
1122 void Bvfs::clear_cache()
1124 db_sql_query(db, "BEGIN", NULL, NULL);
1125 db_sql_query(db, "UPDATE Job SET HasCache=0", NULL, NULL);
1126 db_sql_query(db, "TRUNCATE PathHierarchy", NULL, NULL);
1127 db_sql_query(db, "TRUNCATE PathVisibility", NULL, NULL);
1128 db_sql_query(db, "COMMIT", NULL, NULL);
1131 bool Bvfs::drop_restore_list(char *output_table)
1134 if (check_temp(output_table)) {
1135 Mmsg(query, "DROP TABLE %s", output_table);
1136 db_sql_query(db, query.c_str(), NULL, NULL);
1142 bool Bvfs::compute_restore_list(char *fileid, char *dirid, char *hardlink,
1147 int64_t id, jobid, prev_jobid;
1151 if ((*fileid && !is_a_number_list(fileid)) ||
1152 (*dirid && !is_a_number_list(dirid)) ||
1153 (*hardlink && !is_a_number_list(hardlink))||
1154 (!*hardlink && !*fileid && !*dirid && !*hardlink))
1158 if (!check_temp(output_table)) {
1164 /* Cleanup old tables first */
1165 Mmsg(query, "DROP TABLE btemp%s", output_table);
1166 db_sql_query(db, query.c_str());
1168 Mmsg(query, "DROP TABLE %s", output_table);
1169 db_sql_query(db, query.c_str());
1171 Mmsg(query, "CREATE TABLE btemp%s AS ", output_table);
1173 if (*fileid) { /* Select files with their direct id */
1175 Mmsg(tmp,"SELECT Job.JobId, JobTDate, FileIndex, FilenameId, "
1177 "FROM File JOIN Job USING (JobId) WHERE FileId IN (%s)",
1179 pm_strcat(query, tmp.c_str());
1182 /* Add a directory content */
1183 while (get_next_id_from_list(&dirid, &id) == 1) {
1184 Mmsg(tmp, "SELECT Path FROM Path WHERE PathId=%lld", id);
1186 if (!db_sql_query(db, tmp.c_str(), get_path_handler, (void *)&tmp2)) {
1187 Dmsg0(dbglevel, "Can't search for path\n");
1191 if (!strcmp(tmp2.c_str(), "")) { /* path not found */
1192 Dmsg3(dbglevel, "Path not found %lld q=%s s=%s\n",
1193 id, tmp.c_str(), tmp2.c_str());
1196 /* escape % and _ for LIKE search */
1197 tmp.check_size((strlen(tmp2.c_str())+1) * 2);
1198 char *p = tmp.c_str();
1199 for (char *s = tmp2.c_str(); *s ; s++) {
1200 if (*s == '%' || *s == '_' || *s == '\\') {
1210 size_t len = strlen(tmp.c_str());
1211 tmp2.check_size((len+1) * 2);
1212 db_escape_string(jcr, db, tmp2.c_str(), tmp.c_str(), len);
1215 query.strcat(" UNION ");
1218 Mmsg(tmp, "SELECT Job.JobId, JobTDate, File.FileIndex, File.FilenameId, "
1219 "File.PathId, FileId "
1220 "FROM Path JOIN File USING (PathId) JOIN Job USING (JobId) "
1221 "WHERE Path.Path LIKE '%s' AND File.JobId IN (%s) ",
1222 tmp2.c_str(), jobids);
1223 query.strcat(tmp.c_str());
1226 query.strcat(" UNION ");
1228 /* A directory can have files from a BaseJob */
1229 Mmsg(tmp, "SELECT File.JobId, JobTDate, BaseFiles.FileIndex, "
1230 "File.FilenameId, File.PathId, BaseFiles.FileId "
1232 "JOIN File USING (FileId) "
1233 "JOIN Job ON (BaseFiles.JobId = Job.JobId) "
1234 "JOIN Path USING (PathId) "
1235 "WHERE Path.Path LIKE '%s' AND BaseFiles.JobId IN (%s) ",
1236 tmp2.c_str(), jobids);
1237 query.strcat(tmp.c_str());
1240 /* expect jobid,fileindex */
1242 while (get_next_id_from_list(&hardlink, &jobid) == 1) {
1243 if (get_next_id_from_list(&hardlink, &id) != 1) {
1244 Dmsg0(dbglevel, "hardlink should be two by two\n");
1247 if (jobid != prev_jobid) { /* new job */
1248 if (prev_jobid == 0) { /* first jobid */
1250 query.strcat(" UNION ");
1252 } else { /* end last job, start new one */
1253 tmp.strcat(") UNION ");
1254 query.strcat(tmp.c_str());
1256 Mmsg(tmp, "SELECT Job.JobId, JobTDate, FileIndex, FilenameId, "
1258 "FROM File JOIN Job USING (JobId) WHERE JobId = %lld "
1259 "AND FileIndex IN (%lld", jobid, id);
1262 } else { /* same job, add new findex */
1263 Mmsg(tmp2, ", %lld", id);
1264 tmp.strcat(tmp2.c_str());
1268 if (prev_jobid != 0) { /* end last job */
1270 query.strcat(tmp.c_str());
1274 Dmsg1(dbglevel_sql, "q=%s\n", query.c_str());
1276 if (!db_sql_query(db, query.c_str(), NULL, NULL)) {
1277 Dmsg0(dbglevel, "Can't execute q\n");
1281 Mmsg(query, sql_bvfs_select[db_get_type_index(db)],
1282 output_table, output_table, output_table);
1284 /* TODO: handle jobid filter */
1285 Dmsg1(dbglevel_sql, "q=%s\n", query.c_str());
1286 if (!db_sql_query(db, query.c_str(), NULL, NULL)) {
1287 Dmsg0(dbglevel, "Can't execute q\n");
1292 if (db_get_type_index(db) == SQL_TYPE_MYSQL) {
1293 Mmsg(query, "CREATE INDEX idx_%s ON %s (JobId)",
1294 output_table, output_table);
1295 Dmsg1(dbglevel_sql, "q=%s\n", query.c_str());
1296 if (!db_sql_query(db, query.c_str(), NULL, NULL)) {
1297 Dmsg0(dbglevel, "Can't execute q\n");
1305 Mmsg(query, "DROP TABLE btemp%s", output_table);
1306 db_sql_query(db, query.c_str(), NULL, NULL);
1311 #endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_POSTGRESQL */