2 Bacula® - The Network Backup Solution
4 Copyright (C) 2000-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 * Written by Kern E. Sibbald
21 * Anyone who accesses the database will need to include
26 Here is how database versions work.
28 While I am working on a new release with database changes, the
29 update scripts are in the src/cats directory under the names
30 update_xxx_tables.in. Most of the time, I make database updates
31 in one go and immediately update the version, but not always. If
32 there are going to be several updates as is the case with version
33 1.37, then I will often forgo changing the version until the last
34 update otherwise I will end up with too many versions and a lot
37 When I am pretty sure there will be no more updates, I will
38 change the version from 8 to 9 (in the present case), and when I
39 am 100% sure there will be no more changes, the update script
40 will be copied to the updatedb directory with the correct name
41 (in the present case 8 to 9).
46 /* ==============================================================
48 * What follows are definitions that are used "globally" for all
49 * the different SQL engines and both inside and external to the
56 * Structure used when calling db_get_query_ids()
57 * allows the subroutine to return a list of ids.
59 class dbid_list : public SMARTALLOC {
61 DBId_t *DBId; /* array of DBIds */
62 char *PurgedFiles; /* Array of PurgedFile flags */
63 int num_ids; /* num of ids actually stored */
64 int max_ids; /* size of id array */
65 int num_seen; /* number of ids processed */
66 int tot_ids; /* total to process */
68 dbid_list(); /* in sql.c */
69 ~dbid_list(); /* in sql.c */
72 /* Job information passed to create job record and update
73 * job record at end of job. Note, although this record
74 * contains all the fields found in the Job database record,
75 * it also contains fields found in the JobMedia record.
80 char Job[MAX_NAME_LENGTH]; /* Job unique name */
81 char Name[MAX_NAME_LENGTH]; /* Job base name */
82 int JobType; /* actually char(1) */
83 int JobLevel; /* actually char(1) */
84 int JobStatus; /* actually char(1) */
85 DBId_t ClientId; /* Id of client */
86 DBId_t PoolId; /* Id of pool */
87 DBId_t FileSetId; /* Id of FileSet */
88 DBId_t PriorJobId; /* Id of migrated (prior) job */
89 time_t SchedTime; /* Time job scheduled */
90 time_t StartTime; /* Job start time */
91 time_t EndTime; /* Job termination time of orig job */
92 time_t RealEndTime; /* Job termination time of this job */
93 utime_t JobTDate; /* Backup time/date in seconds */
94 uint32_t VolSessionId;
95 uint32_t VolSessionTime;
98 uint32_t JobMissingFiles;
104 /* Note, FirstIndex, LastIndex, Start/End File and Block
105 * are only used in the JobMedia record.
107 uint32_t FirstIndex; /* First index this Volume */
108 uint32_t LastIndex; /* Last index this Volume */
114 char cSchedTime[MAX_TIME_LENGTH];
115 char cStartTime[MAX_TIME_LENGTH];
116 char cEndTime[MAX_TIME_LENGTH];
117 char cRealEndTime[MAX_TIME_LENGTH];
118 /* Extra stuff not in DB */
119 int limit; /* limit records to display */
121 uint32_t FileIndex; /* added during Verify */
124 /* Job Media information used to create the media records
125 * for each Volume used for the job.
127 /* JobMedia record */
128 struct JOBMEDIA_DBR {
129 DBId_t JobMediaId; /* record id */
130 JobId_t JobId; /* JobId */
131 DBId_t MediaId; /* MediaId */
132 uint32_t FirstIndex; /* First index this Volume */
133 uint32_t LastIndex; /* Last index this Volume */
134 uint32_t StartFile; /* File for start of data */
135 uint32_t EndFile; /* End file on Volume */
136 uint32_t StartBlock; /* start block on tape */
137 uint32_t EndBlock; /* last block */
138 // uint32_t Copy; /* identical copy */
142 /* Volume Parameter structure */
144 char VolumeName[MAX_NAME_LENGTH]; /* Volume name */
145 char MediaType[MAX_NAME_LENGTH]; /* Media Type */
146 char Storage[MAX_NAME_LENGTH]; /* Storage name */
147 uint32_t VolIndex; /* Volume seqence no. */
148 uint32_t FirstIndex; /* First index this Volume */
149 uint32_t LastIndex; /* Last index this Volume */
150 int32_t Slot; /* Slot */
151 uint64_t StartAddr; /* Start address */
152 uint64_t EndAddr; /* End address */
153 int32_t InChanger; /* InChanger flag */
154 // uint32_t Copy; /* identical copy */
155 // uint32_t Stripe; /* RAIT strip number */
159 /* Attributes record -- NOT same as in database because
160 * in general, this "record" creates multiple database
161 * records (e.g. pathname, filename, fileattributes).
164 char *fname; /* full path & filename */
165 char *link; /* link if any */
166 char *attr; /* attributes statp */
185 uint32_t object_full_len;
186 uint32_t object_index;
187 int32_t object_compression;
192 DBId_t RestoreObjectId;
196 /* File record -- same format as database */
206 char Digest[BASE64_SIZE(CRYPTO_DIGEST_MAX_SIZE)];
207 int DigestType; /* NO_SIG/MD5_SIG/SHA1_SIG */
210 /* Pool record -- same format as database */
213 char Name[MAX_NAME_LENGTH]; /* Pool name */
214 uint32_t NumVols; /* total number of volumes */
215 uint32_t MaxVols; /* max allowed volumes */
216 int32_t LabelType; /* Bacula/ANSI/IBM */
217 int32_t UseOnce; /* set to use once only */
218 int32_t UseCatalog; /* set to use catalog */
219 int32_t AcceptAnyVolume; /* set to accept any volume sequence */
220 int32_t AutoPrune; /* set to prune automatically */
221 int32_t Recycle; /* default Vol recycle flag */
222 uint32_t ActionOnPurge; /* action on purge, e.g. truncate the disk volume */
223 utime_t VolRetention; /* retention period in seconds */
224 utime_t VolUseDuration; /* time in secs volume can be used */
225 uint32_t MaxVolJobs; /* Max Jobs on Volume */
226 uint32_t MaxVolFiles; /* Max files on Volume */
227 uint64_t MaxVolBytes; /* Max bytes on Volume */
228 DBId_t RecyclePoolId; /* RecyclePool destination when media is purged */
229 DBId_t ScratchPoolId; /* ScratchPool source when media is needed */
230 char PoolType[MAX_NAME_LENGTH];
231 char LabelFormat[MAX_NAME_LENGTH];
232 /* Extra stuff not in DB */
239 char Name[MAX_NAME_LENGTH]; /* Device name */
240 DBId_t MediaTypeId; /* MediaType */
241 DBId_t StorageId; /* Storage id if autochanger */
242 uint32_t DevMounts; /* Number of times mounted */
243 uint32_t DevErrors; /* Number of read/write errors */
244 uint64_t DevReadBytes; /* Number of bytes read */
245 uint64_t DevWriteBytes; /* Number of bytew written */
246 uint64_t DevReadTime; /* time spent reading volume */
247 uint64_t DevWriteTime; /* time spent writing volume */
248 uint64_t DevReadTimeSincCleaning; /* read time since cleaning */
249 uint64_t DevWriteTimeSincCleaning; /* write time since cleaning */
250 time_t CleaningDate; /* time last cleaned */
251 utime_t CleaningPeriod; /* time between cleanings */
257 char Name[MAX_NAME_LENGTH]; /* Device name */
258 int AutoChanger; /* Set if autochanger */
260 /* Not in database */
261 bool created; /* set if created by db_create ... */
264 class MEDIATYPE_DBR {
267 char MediaType[MAX_NAME_LENGTH]; /* MediaType string */
268 int ReadOnly; /* Set if read-only */
272 /* Media record -- same as the database */
275 MEDIA_DBR() { memset(this, 0, sizeof(MEDIA_DBR)); };
277 void clear() { memset(this, 0, sizeof(MEDIA_DBR)); };
278 void copy(MEDIA_DBR *omr) { memcpy(this, omr, sizeof(MEDIA_DBR)); };
280 DBId_t MediaId; /* Unique volume id */
281 char VolumeName[MAX_NAME_LENGTH]; /* Volume name */
282 char MediaType[MAX_NAME_LENGTH]; /* Media type */
283 DBId_t PoolId; /* Pool id */
284 time_t FirstWritten; /* Time Volume first written this usage */
285 time_t LastWritten; /* Time Volume last written */
286 time_t LabelDate; /* Date/Time Volume labeled */
287 time_t InitialWrite; /* Date/Time Volume first written */
288 int32_t LabelType; /* Label (Bacula/ANSI/IBM) */
289 uint32_t VolJobs; /* number of jobs on this medium */
290 uint32_t VolFiles; /* Number of files */
291 uint32_t VolBlocks; /* Number of blocks */
292 uint32_t VolMounts; /* Number of times mounted */
293 uint32_t VolErrors; /* Number of read/write errors */
294 uint32_t VolWrites; /* Number of writes */
295 uint32_t VolReads; /* Number of reads */
296 uint64_t VolBytes; /* Number of bytes written */
297 uint32_t VolParts; /* Number of parts written */
298 uint64_t MaxVolBytes; /* Max bytes to write to Volume */
299 uint64_t VolCapacityBytes; /* capacity estimate */
300 uint64_t VolReadTime; /* time spent reading volume */
301 uint64_t VolWriteTime; /* time spent writing volume */
302 utime_t VolRetention; /* Volume retention in seconds */
303 utime_t VolUseDuration; /* time in secs volume can be used */
304 uint32_t ActionOnPurge; /* action on purge, e.g. truncate the disk volume */
305 uint32_t MaxVolJobs; /* Max Jobs on Volume */
306 uint32_t MaxVolFiles; /* Max files on Volume */
307 int32_t Recycle; /* recycle yes/no */
308 int32_t Slot; /* slot in changer */
309 int32_t Enabled; /* 0=disabled, 1=enabled, 2=archived */
310 int32_t InChanger; /* Volume currently in changer */
311 DBId_t StorageId; /* Storage record Id */
312 uint32_t EndFile; /* Last file on volume */
313 uint32_t EndBlock; /* Last block on volume */
314 uint32_t RecycleCount; /* Number of times recycled */
315 char VolStatus[20]; /* Volume status */
316 DBId_t DeviceId; /* Device where Vol last written */
317 DBId_t LocationId; /* Where Volume is -- user defined */
318 DBId_t ScratchPoolId; /* Where to move if scratch */
319 DBId_t RecyclePoolId; /* Where to move when recycled */
320 /* Extra stuff not in DB */
321 faddr_t rec_addr; /* found record address */
322 /* Since the database returns times as strings, this is how we pass
325 char cFirstWritten[MAX_TIME_LENGTH]; /* FirstWritten returned from DB */
326 char cLastWritten[MAX_TIME_LENGTH]; /* LastWritten returned from DB */
327 char cLabelDate[MAX_TIME_LENGTH]; /* LabelData returned from DB */
328 char cInitialWrite[MAX_TIME_LENGTH]; /* InitialWrite returned from DB */
329 bool set_first_written;
333 /* Client record -- same as the database */
335 DBId_t ClientId; /* Unique Client id */
337 utime_t FileRetention;
338 utime_t JobRetention;
339 char Name[MAX_NAME_LENGTH]; /* Client name */
340 char Uname[256]; /* Uname for client */
343 /* Counter record as in database */
345 char Counter[MAX_NAME_LENGTH];
348 int32_t CurrentValue;
349 char WrapCounter[MAX_NAME_LENGTH];
353 /* FileSet record -- same as the database */
355 DBId_t FileSetId; /* Unique FileSet id */
356 char FileSet[MAX_NAME_LENGTH]; /* FileSet name */
357 char MD5[50]; /* MD5 signature of include/exclude */
358 time_t CreateTime; /* date created */
360 * This is where we return CreateTime
362 char cCreateTime[MAX_TIME_LENGTH]; /* CreateTime as returned from DB */
363 /* Not in DB but returned by db_create_fileset() */
364 bool created; /* set when record newly created */
367 /* Call back context for getting a 32/64 bit value from the database */
370 int64_t value; /* value returned */
371 int count; /* number of values seen */
373 db_int64_ctx() : value(0), count(0) {};
376 db_int64_ctx(const db_int64_ctx&); /* prohibit pass by value */
377 db_int64_ctx &operator=(const db_int64_ctx&); /* prohibit class assignment */
380 /* Call back context for getting a list of comma separated strings from the
385 POOLMEM *list; /* list */
386 int count; /* number of values seen */
388 db_list_ctx() { list = get_pool_memory(PM_FNAME); reset(); }
389 ~db_list_ctx() { free_pool_memory(list); list = NULL; }
390 void reset() { *list = 0; count = 0;}
391 void add(const db_list_ctx &str) {
394 pm_strcat(list, ",");
396 pm_strcat(list, str.list);
400 void add(const char *str) {
402 pm_strcat(list, ",");
404 pm_strcat(list, str);
408 db_list_ctx(const db_list_ctx&); /* prohibit pass by value */
409 db_list_ctx &operator=(const db_list_ctx&); /* prohibit class assignment */
413 SQL_INTERFACE_TYPE_MYSQL = 0,
414 SQL_INTERFACE_TYPE_POSTGRESQL = 1,
415 SQL_INTERFACE_TYPE_SQLITE3 = 2,
420 SQL_TYPE_POSTGRESQL = 1,
421 SQL_TYPE_SQLITE3 = 2,
422 SQL_TYPE_UNKNOWN = 99
425 typedef void (DB_LIST_HANDLER)(void *, const char *);
426 typedef int (DB_RESULT_HANDLER)(void *, int, char **);
428 #define db_lock(mdb) mdb->_db_lock(__FILE__, __LINE__)
429 #define db_unlock(mdb) mdb->_db_unlock(__FILE__, __LINE__)
431 /* Current database version number for all drivers */
432 #define BDB_VERSION 14
434 class B_DB: public SMARTALLOC {
436 brwlock_t m_lock; /* transaction lock */
437 dlink m_link; /* queue control */
438 SQL_INTERFACETYPE m_db_interface_type; /* type of backend used */
439 SQL_DBTYPE m_db_type; /* database type */
440 int m_ref_count; /* reference count */
441 bool m_connected; /* connection made to db */
442 bool m_have_batch_insert; /* have batch insert support ? */
443 char *m_db_driver; /* database driver */
444 char *m_db_driverdir; /* database driver dir */
445 char *m_db_name; /* database name */
446 char *m_db_user; /* database user */
447 char *m_db_address; /* host name address */
448 char *m_db_socket; /* socket for local access */
449 char *m_db_password; /* database password */
450 int m_db_port; /* port for host name address */
451 bool m_disabled_batch_insert; /* explicitly disabled batch insert mode ? */
452 bool m_dedicated; /* is this connection dedicated? */
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 int 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_ */