2 Bacula® - The Network Backup Solution
4 Copyright (C) 2000-2011 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 and included
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.
33 * Anyone who accesses the database will need to include
38 Here is how database versions work.
40 While I am working on a new release with database changes, the
41 update scripts are in the src/cats directory under the names
42 update_xxx_tables.in. Most of the time, I make database updates
43 in one go and immediately update the version, but not always. If
44 there are going to be several updates as is the case with version
45 1.37, then I will often forgo changing the version until the last
46 update otherwise I will end up with too many versions and a lot
49 When I am pretty sure there will be no more updates, I will
50 change the version from 8 to 9 (in the present case), and when I
51 am 100% sure there will be no more changes, the update script
52 will be copied to the updatedb directory with the correct name
53 (in the present case 8 to 9).
58 /* ==============================================================
60 * What follows are definitions that are used "globally" for all
61 * the different SQL engines and both inside and external to the
68 * Structure used when calling db_get_query_ids()
69 * allows the subroutine to return a list of ids.
71 class dbid_list : public SMARTALLOC {
73 DBId_t *DBId; /* array of DBIds */
74 char *PurgedFiles; /* Array of PurgedFile flags */
75 int num_ids; /* num of ids actually stored */
76 int max_ids; /* size of id array */
77 int num_seen; /* number of ids processed */
78 int tot_ids; /* total to process */
80 dbid_list(); /* in sql.c */
81 ~dbid_list(); /* in sql.c */
84 /* Job information passed to create job record and update
85 * job record at end of job. Note, although this record
86 * contains all the fields found in the Job database record,
87 * it also contains fields found in the JobMedia record.
92 char Job[MAX_NAME_LENGTH]; /* Job unique name */
93 char Name[MAX_NAME_LENGTH]; /* Job base name */
94 int JobType; /* actually char(1) */
95 int JobLevel; /* actually char(1) */
96 int JobStatus; /* actually char(1) */
97 DBId_t ClientId; /* Id of client */
98 DBId_t PoolId; /* Id of pool */
99 DBId_t FileSetId; /* Id of FileSet */
100 DBId_t PriorJobId; /* Id of migrated (prior) job */
101 time_t SchedTime; /* Time job scheduled */
102 time_t StartTime; /* Job start time */
103 time_t EndTime; /* Job termination time of orig job */
104 time_t RealEndTime; /* Job termination time of this job */
105 utime_t JobTDate; /* Backup time/date in seconds */
106 uint32_t VolSessionId;
107 uint32_t VolSessionTime;
110 uint32_t JobMissingFiles;
116 /* Note, FirstIndex, LastIndex, Start/End File and Block
117 * are only used in the JobMedia record.
119 uint32_t FirstIndex; /* First index this Volume */
120 uint32_t LastIndex; /* Last index this Volume */
126 char cSchedTime[MAX_TIME_LENGTH];
127 char cStartTime[MAX_TIME_LENGTH];
128 char cEndTime[MAX_TIME_LENGTH];
129 char cRealEndTime[MAX_TIME_LENGTH];
130 /* Extra stuff not in DB */
131 int limit; /* limit records to display */
133 uint32_t FileIndex; /* added during Verify */
136 /* Job Media information used to create the media records
137 * for each Volume used for the job.
139 /* JobMedia record */
140 struct JOBMEDIA_DBR {
141 DBId_t JobMediaId; /* record id */
142 JobId_t JobId; /* JobId */
143 DBId_t MediaId; /* MediaId */
144 uint32_t FirstIndex; /* First index this Volume */
145 uint32_t LastIndex; /* Last index this Volume */
146 uint32_t StartFile; /* File for start of data */
147 uint32_t EndFile; /* End file on Volume */
148 uint32_t StartBlock; /* start block on tape */
149 uint32_t EndBlock; /* last block */
150 // uint32_t Copy; /* identical copy */
154 /* Volume Parameter structure */
156 char VolumeName[MAX_NAME_LENGTH]; /* Volume name */
157 char MediaType[MAX_NAME_LENGTH]; /* Media Type */
158 char Storage[MAX_NAME_LENGTH]; /* Storage name */
159 uint32_t VolIndex; /* Volume seqence no. */
160 uint32_t FirstIndex; /* First index this Volume */
161 uint32_t LastIndex; /* Last index this Volume */
162 int32_t Slot; /* Slot */
163 uint64_t StartAddr; /* Start address */
164 uint64_t EndAddr; /* End address */
165 int32_t InChanger; /* InChanger flag */
166 // uint32_t Copy; /* identical copy */
167 // uint32_t Stripe; /* RAIT strip number */
171 /* Attributes record -- NOT same as in database because
172 * in general, this "record" creates multiple database
173 * records (e.g. pathname, filename, fileattributes).
176 char *fname; /* full path & filename */
177 char *link; /* link if any */
178 char *attr; /* attributes statp */
197 uint32_t object_full_len;
198 uint32_t object_index;
199 int32_t object_compression;
204 DBId_t RestoreObjectId;
208 /* File record -- same format as database */
217 char Digest[BASE64_SIZE(CRYPTO_DIGEST_MAX_SIZE)];
218 int DigestType; /* NO_SIG/MD5_SIG/SHA1_SIG */
221 /* Pool record -- same format as database */
224 char Name[MAX_NAME_LENGTH]; /* Pool name */
225 uint32_t NumVols; /* total number of volumes */
226 uint32_t MaxVols; /* max allowed volumes */
227 int32_t LabelType; /* Bacula/ANSI/IBM */
228 int32_t UseOnce; /* set to use once only */
229 int32_t UseCatalog; /* set to use catalog */
230 int32_t AcceptAnyVolume; /* set to accept any volume sequence */
231 int32_t AutoPrune; /* set to prune automatically */
232 int32_t Recycle; /* default Vol recycle flag */
233 uint32_t ActionOnPurge; /* action on purge, e.g. truncate the disk volume */
234 utime_t VolRetention; /* retention period in seconds */
235 utime_t VolUseDuration; /* time in secs volume can be used */
236 uint32_t MaxVolJobs; /* Max Jobs on Volume */
237 uint32_t MaxVolFiles; /* Max files on Volume */
238 uint64_t MaxVolBytes; /* Max bytes on Volume */
239 DBId_t RecyclePoolId; /* RecyclePool destination when media is purged */
240 DBId_t ScratchPoolId; /* ScratchPool source when media is needed */
241 char PoolType[MAX_NAME_LENGTH];
242 char LabelFormat[MAX_NAME_LENGTH];
243 /* Extra stuff not in DB */
250 char Name[MAX_NAME_LENGTH]; /* Device name */
251 DBId_t MediaTypeId; /* MediaType */
252 DBId_t StorageId; /* Storage id if autochanger */
253 uint32_t DevMounts; /* Number of times mounted */
254 uint32_t DevErrors; /* Number of read/write errors */
255 uint64_t DevReadBytes; /* Number of bytes read */
256 uint64_t DevWriteBytes; /* Number of bytew written */
257 uint64_t DevReadTime; /* time spent reading volume */
258 uint64_t DevWriteTime; /* time spent writing volume */
259 uint64_t DevReadTimeSincCleaning; /* read time since cleaning */
260 uint64_t DevWriteTimeSincCleaning; /* write time since cleaning */
261 time_t CleaningDate; /* time last cleaned */
262 utime_t CleaningPeriod; /* time between cleanings */
268 char Name[MAX_NAME_LENGTH]; /* Device name */
269 int AutoChanger; /* Set if autochanger */
271 /* Not in database */
272 bool created; /* set if created by db_create ... */
275 class MEDIATYPE_DBR {
278 char MediaType[MAX_NAME_LENGTH]; /* MediaType string */
279 int ReadOnly; /* Set if read-only */
283 /* Media record -- same as the database */
285 DBId_t MediaId; /* Unique volume id */
286 char VolumeName[MAX_NAME_LENGTH]; /* Volume name */
287 char MediaType[MAX_NAME_LENGTH]; /* Media type */
288 DBId_t PoolId; /* Pool id */
289 time_t FirstWritten; /* Time Volume first written this usage */
290 time_t LastWritten; /* Time Volume last written */
291 time_t LabelDate; /* Date/Time Volume labeled */
292 time_t InitialWrite; /* Date/Time Volume first written */
293 int32_t LabelType; /* Label (Bacula/ANSI/IBM) */
294 uint32_t VolJobs; /* number of jobs on this medium */
295 uint32_t VolFiles; /* Number of files */
296 uint32_t VolBlocks; /* Number of blocks */
297 uint32_t VolMounts; /* Number of times mounted */
298 uint32_t VolErrors; /* Number of read/write errors */
299 uint32_t VolWrites; /* Number of writes */
300 uint32_t VolReads; /* Number of reads */
301 uint64_t VolBytes; /* Number of bytes written */
302 uint32_t VolParts; /* Number of parts written */
303 uint64_t MaxVolBytes; /* Max bytes to write to Volume */
304 uint64_t VolCapacityBytes; /* capacity estimate */
305 uint64_t VolReadTime; /* time spent reading volume */
306 uint64_t VolWriteTime; /* time spent writing volume */
307 utime_t VolRetention; /* Volume retention in seconds */
308 utime_t VolUseDuration; /* time in secs volume can be used */
309 uint32_t ActionOnPurge; /* action on purge, e.g. truncate the disk volume */
310 uint32_t MaxVolJobs; /* Max Jobs on Volume */
311 uint32_t MaxVolFiles; /* Max files on Volume */
312 int32_t Recycle; /* recycle yes/no */
313 int32_t Slot; /* slot in changer */
314 int32_t Enabled; /* 0=disabled, 1=enabled, 2=archived */
315 int32_t InChanger; /* Volume currently in changer */
316 DBId_t StorageId; /* Storage record Id */
317 uint32_t EndFile; /* Last file on volume */
318 uint32_t EndBlock; /* Last block on volume */
319 uint32_t RecycleCount; /* Number of times recycled */
320 char VolStatus[20]; /* Volume status */
321 DBId_t DeviceId; /* Device where Vol last written */
322 DBId_t LocationId; /* Where Volume is -- user defined */
323 DBId_t ScratchPoolId; /* Where to move if scratch */
324 DBId_t RecyclePoolId; /* Where to move when recycled */
325 /* Extra stuff not in DB */
326 faddr_t rec_addr; /* found record address */
327 /* Since the database returns times as strings, this is how we pass
330 char cFirstWritten[MAX_TIME_LENGTH]; /* FirstWritten returned from DB */
331 char cLastWritten[MAX_TIME_LENGTH]; /* LastWritten returned from DB */
332 char cLabelDate[MAX_TIME_LENGTH]; /* LabelData returned from DB */
333 char cInitialWrite[MAX_TIME_LENGTH]; /* InitialWrite returned from DB */
334 bool set_first_written;
338 /* Client record -- same as the database */
340 DBId_t ClientId; /* Unique Client id */
342 utime_t FileRetention;
343 utime_t JobRetention;
344 char Name[MAX_NAME_LENGTH]; /* Client name */
345 char Uname[256]; /* Uname for client */
348 /* Counter record as in database */
350 char Counter[MAX_NAME_LENGTH];
353 int32_t CurrentValue;
354 char WrapCounter[MAX_NAME_LENGTH];
358 /* FileSet record -- same as the database */
360 DBId_t FileSetId; /* Unique FileSet id */
361 char FileSet[MAX_NAME_LENGTH]; /* FileSet name */
362 char MD5[50]; /* MD5 signature of include/exclude */
363 time_t CreateTime; /* date created */
365 * This is where we return CreateTime
367 char cCreateTime[MAX_TIME_LENGTH]; /* CreateTime as returned from DB */
368 /* Not in DB but returned by db_create_fileset() */
369 bool created; /* set when record newly created */
372 /* Call back context for getting a 32/64 bit value from the database */
373 struct db_int64_ctx {
374 int64_t value; /* value returned */
375 int count; /* number of values seen */
378 /* Call back context for getting a list of comma separated strings from the
383 POOLMEM *list; /* list */
384 int count; /* number of values seen */
386 db_list_ctx() { list = get_pool_memory(PM_FNAME); reset(); }
387 ~db_list_ctx() { free_pool_memory(list); list = NULL; }
388 void reset() { *list = 0; count = 0;}
389 void add(const db_list_ctx &str) {
392 pm_strcat(list, ",");
394 pm_strcat(list, str.list);
398 void add(const char *str) {
400 pm_strcat(list, ",");
402 pm_strcat(list, str);
406 db_list_ctx(const db_list_ctx&); /* prohibit pass by value */
407 db_list_ctx &operator=(const db_list_ctx&); /* prohibit class assignment */
411 SQL_INTERFACE_TYPE_MYSQL = 0,
412 SQL_INTERFACE_TYPE_POSTGRESQL = 1,
413 SQL_INTERFACE_TYPE_SQLITE3 = 2,
414 SQL_INTERFACE_TYPE_INGRES = 3,
415 SQL_INTERFACE_TYPE_DBI = 4
420 SQL_TYPE_POSTGRESQL = 1,
421 SQL_TYPE_SQLITE3 = 2,
423 SQL_TYPE_UNKNOWN = 99
426 typedef void (DB_LIST_HANDLER)(void *, const char *);
427 typedef int (DB_RESULT_HANDLER)(void *, int, char **);
429 #define db_lock(mdb) mdb->_db_lock(__FILE__, __LINE__)
430 #define db_unlock(mdb) mdb->_db_unlock(__FILE__, __LINE__)
432 /* Current database version number for all drivers */
433 #define BDB_VERSION 13
435 class B_DB: public SMARTALLOC {
437 brwlock_t m_lock; /* transaction lock */
438 dlink m_link; /* queue control */
439 SQL_INTERFACETYPE m_db_interface_type; /* type of backend used */
440 SQL_DBTYPE m_db_type; /* database type */
441 int m_ref_count; /* reference count */
442 bool m_connected; /* connection made to db */
443 bool m_have_batch_insert; /* have batch insert support ? */
444 char *m_db_driver; /* database driver */
445 char *m_db_driverdir; /* database driver dir */
446 char *m_db_name; /* database name */
447 char *m_db_user; /* database user */
448 char *m_db_address; /* host name address */
449 char *m_db_socket; /* socket for local access */
450 char *m_db_password; /* database password */
451 int m_db_port; /* port for host name address */
452 bool m_disabled_batch_insert; /* explicitly disabled batch insert mode ? */
455 POOLMEM *errmsg; /* nicely edited error message */
456 POOLMEM *cmd; /* SQL command string */
457 POOLMEM *cached_path; /* cached path name */
458 int cached_path_len; /* length of cached path */
459 uint32_t cached_path_id; /* cached path id */
460 int changes; /* changes during transaction */
461 POOLMEM *fname; /* Filename only */
462 POOLMEM *path; /* Path only */
463 POOLMEM *esc_name; /* Escaped file name */
464 POOLMEM *esc_path; /* Escaped path name */
465 POOLMEM *esc_obj; /* Escaped restore object */
466 int fnl; /* file name length */
467 int pnl; /* path name length */
472 const char *get_db_name(void) { return m_db_name; };
473 const char *get_db_user(void) { return m_db_user; };
474 bool is_connected(void) { return m_connected; };
475 bool batch_insert_available(void) { return m_have_batch_insert; };
476 void increment_refcount(void) { m_ref_count++; };
478 /* low level methods */
479 bool db_match_database(const char *db_driver, const char *db_name,
480 const char *db_address, int db_port);
481 B_DB *db_clone_database_connection(JCR *jcr, bool mult_db_connections);
482 int db_get_type_index(void) { return m_db_type; };
483 const char *db_get_type(void);
484 void _db_lock(const char *file, int line);
485 void _db_unlock(const char *file, int line);
486 bool db_sql_query(const char *query, int flags=0);
487 void print_lock_info(FILE *fp);
489 /* Pure virtual low level methods */
490 virtual bool db_open_database(JCR *jcr) = 0;
491 virtual void db_close_database(JCR *jcr) = 0;
492 virtual void db_thread_cleanup(void) = 0;
493 virtual void db_escape_string(JCR *jcr, char *snew, char *old, int len) = 0;
494 virtual char *db_escape_object(JCR *jcr, char *old, int len) = 0;
495 virtual void db_unescape_object(JCR *jcr, char *from, int32_t expected_len,
496 POOLMEM **dest, int32_t *len) = 0;
497 virtual void db_start_transaction(JCR *jcr) = 0;
498 virtual void db_end_transaction(JCR *jcr) = 0;
499 virtual bool db_sql_query(const char *query, DB_RESULT_HANDLER *result_handler, void *ctx) = 0;
501 /* By default, we use db_sql_query */
502 virtual bool db_big_sql_query(const char *query,
503 DB_RESULT_HANDLER *result_handler, void *ctx) {
504 return db_sql_query(query, result_handler, ctx);
508 /* sql_query Query Flags */
509 #define QF_STORE_RESULT 0x01
511 /* Use for better error location printing */
512 #define UPDATE_DB(jcr, db, cmd) UpdateDB(__FILE__, __LINE__, jcr, db, cmd)
513 #define INSERT_DB(jcr, db, cmd) InsertDB(__FILE__, __LINE__, jcr, db, cmd)
514 #define QUERY_DB(jcr, db, cmd) QueryDB(__FILE__, __LINE__, jcr, db, cmd)
515 #define DELETE_DB(jcr, db, cmd) DeleteDB(__FILE__, __LINE__, jcr, db, cmd)
519 #include "sql_cmds.h"
521 /* Object used in db_list_xxx function */
524 char line[256]; /* Used to print last dash line */
527 e_list_type type; /* Vertical/Horizontal */
528 DB_LIST_HANDLER *send; /* send data back */
529 bool once; /* Used to print header one time */
530 void *ctx; /* send() user argument */
545 LIST_CTX(JCR *j, B_DB *m, DB_LIST_HANDLER *h, void *c, e_list_type t) {
558 * Some functions exported by sql.c for use within the cats directory.
560 int list_result(void *vctx, int cols, char **row);
561 void list_result(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *send, void *ctx, e_list_type type);
562 void list_dashes(B_DB *mdb, DB_LIST_HANDLER *send, void *ctx);
563 int get_sql_record_max(JCR *jcr, B_DB *mdb);
564 bool check_tables_version(JCR *jcr, B_DB *mdb);
565 bool db_check_max_connections(JCR *jcr, B_DB *mdb, uint32_t nb);
567 void print_dashes(B_DB *mdb);
568 void print_result(B_DB *mdb);
569 int QueryDB(const char *file, int line, JCR *jcr, B_DB *db, char *select_cmd);
570 int InsertDB(const char *file, int line, JCR *jcr, B_DB *db, char *select_cmd);
571 int DeleteDB(const char *file, int line, JCR *jcr, B_DB *db, char *delete_cmd);
572 int UpdateDB(const char *file, int line, JCR *jcr, B_DB *db, char *update_cmd);
573 void split_path_and_file(JCR *jcr, B_DB *mdb, const char *fname);
574 #endif /* __CATS_H_ */