2 Bacula® - The Network Backup Solution
4 Copyright (C) 2009-2009 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 two of the GNU 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 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 prev_dir = get_pool_memory(PM_NAME);
62 pattern = get_pool_memory(PM_NAME);
63 *prev_dir = *pattern = 0;
64 dir_filenameid = pwd_id = offset = 0;
65 see_copies = see_all_version = false;
68 list_entries = result_handler;
73 free_pool_memory(pattern);
74 free_pool_memory(prev_dir);
80 * TODO: Find a way to let the user choose how he wants to display
81 * files and directories
86 * Working Object to store PathId already seen (avoid
87 * database queries), equivalent to %cache_ppathid in perl
96 htable *cache_ppathid;
101 cache_ppathid = (htable *)malloc(sizeof(htable));
102 cache_ppathid->init(&link, &link, NITEMS);
104 nodes = (hlink *) malloc(max_node * sizeof (hlink));
109 if (nb_node >= max_node) {
111 nodes = (hlink *)brealloc(nodes, sizeof(hlink) * max_node);
113 return nodes + nb_node++;
116 bool lookup(char *pathid) {
117 bool ret = cache_ppathid->lookup(pathid) != NULL;
121 void insert(char *pathid) {
122 hlink *h = get_hlink();
123 cache_ppathid->insert(pathid, h);
127 cache_ppathid->destroy();
133 /* Return the parent_dir with the trailing / (update the given string)
134 * TODO: see in the rest of bacula if we don't have already this function
140 char *bvfs_parent_dir(char *path)
143 int len = strlen(path) - 1;
145 if (len >= 0 && path[len] == '/') { /* if directory, skip last / */
151 while (p > path && !IsPathSeparator(*p)) {
159 /* Return the basename of the with the trailing /
160 * TODO: see in the rest of bacula if we don't have
161 * this function already
163 char *bvfs_basename_dir(char *path)
166 int len = strlen(path) - 1;
168 if (path[len] == '/') { /* if directory, skip last / */
174 while (p > path && !IsPathSeparator(*p)) {
178 p++; /* skip first / */
184 static void build_path_hierarchy(JCR *jcr, B_DB *mdb,
185 pathid_cache &ppathid_cache,
186 char *org_pathid, char *path)
188 Dmsg1(dbglevel, "build_path_hierarchy(%s)\n", path);
191 char *bkp = mdb->path;
192 strncpy(pathid, org_pathid, sizeof(pathid));
194 /* Does the ppathid exist for this ? we use a memory cache... In order to
195 * avoid the full loop, we consider that if a dir is allready in the
196 * brestore_pathhierarchy table, then there is no need to calculate all the
199 while (path && *path)
201 if (!ppathid_cache.lookup(pathid))
204 "SELECT PPathId FROM brestore_pathhierarchy WHERE PathId = %s",
207 QUERY_DB(jcr, mdb, mdb->cmd);
208 /* Do we have a result ? */
209 if (sql_num_rows(mdb) > 0) {
210 ppathid_cache.insert(pathid);
211 /* This dir was in the db ...
212 * It means we can leave, the tree has allready been built for
217 /* search or create parent PathId in Path table */
218 mdb->path = bvfs_parent_dir(path);
219 mdb->pnl = strlen(mdb->path);
220 if (!db_create_path_record(jcr, mdb, &parent)) {
223 ppathid_cache.insert(pathid);
226 "INSERT INTO brestore_pathhierarchy (PathId, PPathId) "
228 pathid, (uint64_t) parent.PathId);
230 INSERT_DB(jcr, mdb, mdb->cmd);
232 edit_uint64(parent.PathId, pathid);
233 path = mdb->path; /* already done */
236 /* It's allready in the cache. We can leave, no time to waste here,
237 * all the parent dirs have allready been done
249 * Internal function to update path_hierarchy cache with a shared pathid cache
251 static void update_path_hierarchy_cache(JCR *jcr,
253 pathid_cache &ppathid_cache,
256 Dmsg0(dbglevel, "update_path_hierarchy_cache()\n");
260 edit_uint64(JobId, jobid);
263 db_start_transaction(jcr, mdb);
265 Mmsg(mdb->cmd, "SELECT 1 FROM brestore_knownjobid WHERE JobId = %s", jobid);
267 if (!QUERY_DB(jcr, mdb, mdb->cmd) || sql_num_rows(mdb) > 0) {
268 Dmsg1(dbglevel, "already computed %d\n", (uint32_t)JobId );
272 /* Inserting path records for JobId */
273 Mmsg(mdb->cmd, "INSERT INTO brestore_pathvisibility (PathId, JobId) "
274 "SELECT DISTINCT PathId, JobId FROM File WHERE JobId = %s",
276 QUERY_DB(jcr, mdb, mdb->cmd);
279 /* Now we have to do the directory recursion stuff to determine missing
280 * visibility We try to avoid recursion, to be as fast as possible We also
281 * only work on not allready hierarchised directories...
284 "SELECT brestore_pathvisibility.PathId, Path "
285 "FROM brestore_pathvisibility "
286 "JOIN Path ON( brestore_pathvisibility.PathId = Path.PathId) "
287 "LEFT JOIN brestore_pathhierarchy "
288 "ON (brestore_pathvisibility.PathId = brestore_pathhierarchy.PathId) "
289 "WHERE brestore_pathvisibility.JobId = %s "
290 "AND brestore_pathhierarchy.PathId IS NULL "
291 "ORDER BY Path", jobid);
292 Dmsg1(dbglevel_sql, "q=%s\n", mdb->cmd);
293 QUERY_DB(jcr, mdb, mdb->cmd);
295 /* TODO: I need to reuse the DB connection without emptying the result
296 * So, now i'm copying the result in memory to be able to query the
297 * catalog descriptor again.
299 num = sql_num_rows(mdb);
301 char **result = (char **)malloc (num * 2 * sizeof(char *));
305 while((row = sql_fetch_row(mdb))) {
306 result[i++] = bstrdup(row[0]);
307 result[i++] = bstrdup(row[1]);
312 build_path_hierarchy(jcr, mdb, ppathid_cache, result[i], result[i+1]);
321 "INSERT INTO brestore_pathvisibility (PathId, JobId) "
322 "SELECT a.PathId,%s "
324 "SELECT DISTINCT h.PPathId AS PathId "
325 "FROM brestore_pathhierarchy AS h "
326 "JOIN brestore_pathvisibility AS p ON (h.PathId=p.PathId) "
327 "WHERE p.JobId=%s) AS a LEFT JOIN "
329 "FROM brestore_pathvisibility "
330 "WHERE JobId=%s) AS b ON (a.PathId = b.PathId) "
331 "WHERE b.PathId IS NULL", jobid, jobid, jobid);
334 QUERY_DB(jcr, mdb, mdb->cmd);
335 } while (sql_affected_rows(mdb) > 0);
337 Mmsg(mdb->cmd, "INSERT INTO brestore_knownjobid (JobId) VALUES (%s)", jobid);
338 INSERT_DB(jcr, mdb, mdb->cmd);
341 db_end_transaction(jcr, mdb);
346 * Find an store the filename descriptor for empty directories Filename.Name=''
348 DBId_t Bvfs::get_dir_filenameid()
351 if (dir_filenameid) {
352 return dir_filenameid;
355 Mmsg(q, "SELECT FilenameId FROM Filename WHERE Name = ''");
356 db_sql_query(db, q.c_str(), db_int_handler, &id);
358 return dir_filenameid;
361 void bvfs_update_cache(JCR *jcr, B_DB *mdb)
367 db_start_transaction(jcr, mdb);
369 Mmsg(mdb->cmd, "SELECT 1 from brestore_knownjobid LIMIT 1");
370 /* TODO: Add this code in the make_bacula_table script */
371 if (!QUERY_DB(jcr, mdb, mdb->cmd)) {
372 Dmsg0(dbglevel, "Creating cache table\n");
374 "CREATE TABLE brestore_knownjobid ("
375 "JobId integer NOT NULL, "
376 "CONSTRAINT brestore_knownjobid_pkey PRIMARY KEY (JobId))");
377 QUERY_DB(jcr, mdb, mdb->cmd);
380 "CREATE TABLE brestore_pathhierarchy ( "
381 "PathId integer NOT NULL, "
382 "PPathId integer NOT NULL, "
383 "CONSTRAINT brestore_pathhierarchy_pkey "
384 "PRIMARY KEY (PathId))");
385 QUERY_DB(jcr, mdb, mdb->cmd);
388 "CREATE INDEX brestore_pathhierarchy_ppathid "
389 "ON brestore_pathhierarchy (PPathId)");
390 QUERY_DB(jcr, mdb, mdb->cmd);
393 "CREATE TABLE brestore_pathvisibility ("
394 "PathId integer NOT NULL, "
395 "JobId integer NOT NULL, "
396 "Size int8 DEFAULT 0, "
397 "Files int4 DEFAULT 0, "
398 "CONSTRAINT brestore_pathvisibility_pkey "
399 "PRIMARY KEY (JobId, PathId))");
400 QUERY_DB(jcr, mdb, mdb->cmd);
403 "CREATE INDEX brestore_pathvisibility_jobid "
404 "ON brestore_pathvisibility (JobId)");
405 QUERY_DB(jcr, mdb, mdb->cmd);
410 "SELECT JobId from Job "
411 "WHERE JobId NOT IN (SELECT JobId FROM brestore_knownjobid) "
412 "AND Type IN ('B') AND JobStatus IN ('T', 'f', 'A') "
415 db_sql_query(mdb, mdb->cmd, db_list_handler, &jobids);
417 bvfs_update_path_hierarchy_cache(jcr, mdb, &jobids);
419 db_end_transaction(jcr, mdb);
420 db_start_transaction(jcr, mdb);
421 Dmsg0(dbglevel, "Cleaning pathvisibility\n");
423 "DELETE FROM brestore_pathvisibility "
425 "(SELECT 1 FROM Job WHERE JobId=brestore_pathvisibility.JobId)");
426 nb = DELETE_DB(jcr, mdb, mdb->cmd);
427 Dmsg1(dbglevel, "Affected row(s) = %d\n", nb);
429 Dmsg0(dbglevel, "Cleaning knownjobid\n");
431 "DELETE FROM brestore_knownjobid "
433 "(SELECT 1 FROM Job WHERE JobId=brestore_knownjobid.JobId)");
434 nb = DELETE_DB(jcr, mdb, mdb->cmd);
435 Dmsg1(dbglevel, "Affected row(s) = %d\n", nb);
437 db_end_transaction(jcr, mdb);
441 * Update the bvfs cache for given jobids (1,2,3,4)
444 bvfs_update_path_hierarchy_cache(JCR *jcr, B_DB *mdb, db_list_ctx *jobids)
446 pathid_cache ppathid_cache;
450 for (p=jobids->list; ; ) {
451 int stat = get_next_jobid_from_list(&p, &JobId);
452 Dmsg1(dbglevel, "Updating cache for %lld\n", (uint64_t)JobId);
459 Dmsg1(dbglevel, "Updating cache for %lld\n", (uint64_t)JobId);
460 update_path_hierarchy_cache(jcr, mdb, ppathid_cache, JobId);
465 * Update the bvfs cache for current jobids
467 void Bvfs::update_cache()
469 bvfs_update_path_hierarchy_cache(jcr, db, &jobids);
472 /* Change the current directory, returns true if the path exists */
473 bool Bvfs::ch_dir(const char *path)
475 pm_strcpy(db->path, path);
476 db->pnl = strlen(db->path);
477 ch_dir(db_get_path_record(jcr, db));
482 * Get all file versions for a specified client
484 void Bvfs::get_all_file_versions(DBId_t pathid, DBId_t fnid, const char *client)
486 Dmsg3(dbglevel, "get_all_file_versions(%lld, %lld, %s)\n", (uint64_t)pathid,
487 (uint64_t)fnid, client);
488 char ed1[50], ed2[50];
491 Mmsg(q, " AND Job.Type IN ('C', 'B') ");
493 Mmsg(q, " AND Job.Type = 'B' ");
499 "SELECT File.FileId, File.Md5, File.JobId, File.LStat, "
501 "Media.VolumeName, Media.InChanger "
502 "FROM File, Job, Client, JobMedia, Media "
503 "WHERE File.FilenameId = %s "
504 "AND File.PathId=%s "
505 "AND File.JobId = Job.JobId "
506 "AND Job.ClientId = Client.ClientId "
507 "AND Job.JobId = JobMedia.JobId "
508 "AND File.FileIndex >= JobMedia.FirstIndex "
509 "AND File.FileIndex <= JobMedia.LastIndex "
510 "AND JobMedia.MediaId = Media.MediaId "
511 "AND Client.Name = '%s' "
512 "%s ORDER BY FileId LIMIT %d OFFSET %d"
513 ,edit_uint64(fnid, ed1), edit_uint64(pathid, ed2), client, q.c_str(),
515 Dmsg1(dbglevel_sql, "q=%s\n", query.c_str());
516 db_sql_query(db, query.c_str(), list_entries, user_data);
519 DBId_t Bvfs::get_root()
522 return db_get_path_record(jcr, db);
525 static int path_handler(void *ctx, int fields, char **row)
527 Bvfs *fs = (Bvfs *) ctx;
528 return fs->_handle_path(ctx, fields, row);
531 int Bvfs::_handle_path(void *ctx, int fields, char **row)
533 if (fields == BVFS_DIR_RECORD) {
534 /* can have the same path 2 times */
535 if (strcmp(row[BVFS_Name], prev_dir)) {
536 pm_strcpy(prev_dir, row[BVFS_Name]);
537 return list_entries(user_data, fields, row);
544 * Retrieve . and .. information
546 void Bvfs::ls_special_dirs()
548 Dmsg1(dbglevel, "ls_special_dirs(%lld)\n", (uint64_t)pwd_id);
549 char ed1[50], ed2[50];
550 if (jobids.count == 0) {
553 if (!dir_filenameid) {
554 get_dir_filenameid();
557 /* Will fetch directories */
562 "((SELECT PPathId AS PathId, '..' AS Path "
563 "FROM brestore_pathhierarchy "
564 "WHERE PathId = %s) "
566 "(SELECT %s AS PathId, '.' AS Path))",
567 edit_uint64(pwd_id, ed1), ed1);
571 "SELECT tmp.PathId, tmp.Path, JobId, LStat "
572 "FROM %s AS tmp LEFT JOIN ( " // get attributes if any
573 "SELECT File1.PathId AS PathId, File1.JobId AS JobId, "
574 "File1.LStat AS LStat FROM File AS File1 "
575 "WHERE File1.FilenameId = %s "
576 "AND File1.JobId IN (%s)) AS listfile1 "
577 "ON (tmp.PathId = listfile1.PathId) "
578 "ORDER BY tmp.Path, JobId DESC ",
579 query.c_str(), edit_uint64(dir_filenameid, ed2), jobids.list);
581 Dmsg1(dbglevel_sql, "q=%s\n", query2.c_str());
582 db_sql_query(db, query2.c_str(), path_handler, this);
585 /* Returns true if we have dirs to read */
588 Dmsg1(dbglevel, "ls_dirs(%lld)\n", (uint64_t)pwd_id);
589 char ed1[50], ed2[50];
590 if (jobids.count == 0) {
596 Mmsg(filter, " AND Path2.Path %s '%s' ", SQL_MATCH, pattern);
599 if (!dir_filenameid) {
600 get_dir_filenameid();
603 /* the sql query displays same directory multiple time, take the first one */
606 /* Let's retrieve the list of the visible dirs in this dir ...
607 * First, I need the empty filenameid to locate efficiently
608 * the dirs in the file table
609 * my $dir_filenameid = $self->get_dir_filenameid();
611 /* Then we get all the dir entries from File ... */
615 "SELECT PathId, Path, JobId, LStat FROM ( "
616 "SELECT Path1.PathId AS PathId, Path1.Path AS Path, "
617 "lower(Path1.Path) AS lpath, "
618 "listfile1.JobId AS JobId, listfile1.LStat AS LStat "
620 "SELECT DISTINCT brestore_pathhierarchy1.PathId AS PathId "
621 "FROM brestore_pathhierarchy AS brestore_pathhierarchy1 "
622 "JOIN Path AS Path2 "
623 "ON (brestore_pathhierarchy1.PathId = Path2.PathId) "
624 "JOIN brestore_pathvisibility AS brestore_pathvisibility1 "
625 "ON (brestore_pathhierarchy1.PathId = brestore_pathvisibility1.PathId) "
626 "WHERE brestore_pathhierarchy1.PPathId = %s "
627 "AND brestore_pathvisibility1.jobid IN (%s) "
630 "JOIN Path AS Path1 ON (listpath1.PathId = Path1.PathId) "
632 "LEFT JOIN ( " /* get attributes if any */
633 "SELECT File1.PathId AS PathId, File1.JobId AS JobId, "
634 "File1.LStat AS LStat FROM File AS File1 "
635 "WHERE File1.FilenameId = %s "
636 "AND File1.JobId IN (%s)) AS listfile1 "
637 "ON (listpath1.PathId = listfile1.PathId) "
638 ") AS A ORDER BY 2,3 DESC LIMIT %d OFFSET %d",
639 edit_uint64(pwd_id, ed1),
642 edit_uint64(dir_filenameid, ed2),
646 Dmsg1(dbglevel_sql, "q=%s\n", query.c_str());
649 db_sql_query(db, query.c_str(), path_handler, this);
650 nb_record = db->num_rows;
653 return nb_record == limit;
656 /* Returns true if we have files to read */
657 bool Bvfs::ls_files()
659 Dmsg1(dbglevel, "ls_files(%lld)\n", (uint64_t)pwd_id);
661 if (jobids.count == 0) {
671 Mmsg(filter, " AND Filename.Name %s '%s' ", SQL_MATCH, pattern);
675 Mmsg(query, // 0 1 2 3 4
676 "SELECT File.FilenameId, listfiles.Name, File.JobId, File.LStat, listfiles.id "
678 "SELECT Filename.Name as Name, max(File.FileId) as id "
679 "FROM File, Filename "
680 "WHERE File.FilenameId = Filename.FilenameId "
681 "AND Filename.Name != '' "
682 "AND File.PathId = %s "
683 "AND File.JobId IN (%s) "
685 "GROUP BY Filename.Name "
686 "ORDER BY Filename.Name LIMIT %d OFFSET %d "
688 "WHERE File.FileId = listfiles.id",
689 edit_uint64(pwd_id, ed1),
694 Dmsg1(dbglevel_sql, "q=%s\n", query.c_str());
697 db_sql_query(db, query.c_str(), list_entries, user_data);
698 nb_record = db->num_rows;
701 return nb_record == limit;