2 Bacula® - The Network Backup Solution
4 Copyright (C) 2000-2012 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 */
218 char Digest[BASE64_SIZE(CRYPTO_DIGEST_MAX_SIZE)];
219 int DigestType; /* NO_SIG/MD5_SIG/SHA1_SIG */
222 /* Pool record -- same format as database */
225 char Name[MAX_NAME_LENGTH]; /* Pool name */
226 uint32_t NumVols; /* total number of volumes */
227 uint32_t MaxVols; /* max allowed volumes */
228 int32_t LabelType; /* Bacula/ANSI/IBM */
229 int32_t UseOnce; /* set to use once only */
230 int32_t UseCatalog; /* set to use catalog */
231 int32_t AcceptAnyVolume; /* set to accept any volume sequence */
232 int32_t AutoPrune; /* set to prune automatically */
233 int32_t Recycle; /* default Vol recycle flag */
234 uint32_t ActionOnPurge; /* action on purge, e.g. truncate the disk volume */
235 utime_t VolRetention; /* retention period in seconds */
236 utime_t VolUseDuration; /* time in secs volume can be used */
237 uint32_t MaxVolJobs; /* Max Jobs on Volume */
238 uint32_t MaxVolFiles; /* Max files on Volume */
239 uint64_t MaxVolBytes; /* Max bytes on Volume */
240 DBId_t RecyclePoolId; /* RecyclePool destination when media is purged */
241 DBId_t ScratchPoolId; /* ScratchPool source when media is needed */
242 char PoolType[MAX_NAME_LENGTH];
243 char LabelFormat[MAX_NAME_LENGTH];
244 /* Extra stuff not in DB */
251 char Name[MAX_NAME_LENGTH]; /* Device name */
252 DBId_t MediaTypeId; /* MediaType */
253 DBId_t StorageId; /* Storage id if autochanger */
254 uint32_t DevMounts; /* Number of times mounted */
255 uint32_t DevErrors; /* Number of read/write errors */
256 uint64_t DevReadBytes; /* Number of bytes read */
257 uint64_t DevWriteBytes; /* Number of bytew written */
258 uint64_t DevReadTime; /* time spent reading volume */
259 uint64_t DevWriteTime; /* time spent writing volume */
260 uint64_t DevReadTimeSincCleaning; /* read time since cleaning */
261 uint64_t DevWriteTimeSincCleaning; /* write time since cleaning */
262 time_t CleaningDate; /* time last cleaned */
263 utime_t CleaningPeriod; /* time between cleanings */
269 char Name[MAX_NAME_LENGTH]; /* Device name */
270 int AutoChanger; /* Set if autochanger */
272 /* Not in database */
273 bool created; /* set if created by db_create ... */
276 class MEDIATYPE_DBR {
279 char MediaType[MAX_NAME_LENGTH]; /* MediaType string */
280 int ReadOnly; /* Set if read-only */
284 /* Media record -- same as the database */
287 MEDIA_DBR() { memset(this, 0, sizeof(MEDIA_DBR)); };
289 void clear() { memset(this, 0, sizeof(MEDIA_DBR)); };
290 void copy(MEDIA_DBR *omr) { memcpy(this, omr, sizeof(MEDIA_DBR)); };
292 DBId_t MediaId; /* Unique volume id */
293 char VolumeName[MAX_NAME_LENGTH]; /* Volume name */
294 char MediaType[MAX_NAME_LENGTH]; /* Media type */
295 DBId_t PoolId; /* Pool id */
296 time_t FirstWritten; /* Time Volume first written this usage */
297 time_t LastWritten; /* Time Volume last written */
298 time_t LabelDate; /* Date/Time Volume labeled */
299 time_t InitialWrite; /* Date/Time Volume first written */
300 int32_t LabelType; /* Label (Bacula/ANSI/IBM) */
301 uint32_t VolJobs; /* number of jobs on this medium */
302 uint32_t VolFiles; /* Number of files */
303 uint32_t VolBlocks; /* Number of blocks */
304 uint32_t VolMounts; /* Number of times mounted */
305 uint32_t VolErrors; /* Number of read/write errors */
306 uint32_t VolWrites; /* Number of writes */
307 uint32_t VolReads; /* Number of reads */
308 uint64_t VolBytes; /* Number of bytes written */
309 uint32_t VolParts; /* Number of parts written */
310 uint64_t MaxVolBytes; /* Max bytes to write to Volume */
311 uint64_t VolCapacityBytes; /* capacity estimate */
312 uint64_t VolReadTime; /* time spent reading volume */
313 uint64_t VolWriteTime; /* time spent writing volume */
314 utime_t VolRetention; /* Volume retention in seconds */
315 utime_t VolUseDuration; /* time in secs volume can be used */
316 uint32_t ActionOnPurge; /* action on purge, e.g. truncate the disk volume */
317 uint32_t MaxVolJobs; /* Max Jobs on Volume */
318 uint32_t MaxVolFiles; /* Max files on Volume */
319 int32_t Recycle; /* recycle yes/no */
320 int32_t Slot; /* slot in changer */
321 int32_t Enabled; /* 0=disabled, 1=enabled, 2=archived */
322 int32_t InChanger; /* Volume currently in changer */
323 DBId_t StorageId; /* Storage record Id */
324 uint32_t EndFile; /* Last file on volume */
325 uint32_t EndBlock; /* Last block on volume */
326 uint32_t RecycleCount; /* Number of times recycled */
327 char VolStatus[20]; /* Volume status */
328 DBId_t DeviceId; /* Device where Vol last written */
329 DBId_t LocationId; /* Where Volume is -- user defined */
330 DBId_t ScratchPoolId; /* Where to move if scratch */
331 DBId_t RecyclePoolId; /* Where to move when recycled */
332 /* Extra stuff not in DB */
333 faddr_t rec_addr; /* found record address */
334 /* Since the database returns times as strings, this is how we pass
337 char cFirstWritten[MAX_TIME_LENGTH]; /* FirstWritten returned from DB */
338 char cLastWritten[MAX_TIME_LENGTH]; /* LastWritten returned from DB */
339 char cLabelDate[MAX_TIME_LENGTH]; /* LabelData returned from DB */
340 char cInitialWrite[MAX_TIME_LENGTH]; /* InitialWrite returned from DB */
341 bool set_first_written;
345 /* Client record -- same as the database */
347 DBId_t ClientId; /* Unique Client id */
349 utime_t FileRetention;
350 utime_t JobRetention;
351 char Name[MAX_NAME_LENGTH]; /* Client name */
352 char Uname[256]; /* Uname for client */
355 /* Counter record as in database */
357 char Counter[MAX_NAME_LENGTH];
360 int32_t CurrentValue;
361 char WrapCounter[MAX_NAME_LENGTH];
365 /* FileSet record -- same as the database */
367 DBId_t FileSetId; /* Unique FileSet id */
368 char FileSet[MAX_NAME_LENGTH]; /* FileSet name */
369 char MD5[50]; /* MD5 signature of include/exclude */
370 time_t CreateTime; /* date created */
372 * This is where we return CreateTime
374 char cCreateTime[MAX_TIME_LENGTH]; /* CreateTime as returned from DB */
375 /* Not in DB but returned by db_create_fileset() */
376 bool created; /* set when record newly created */
379 /* Call back context for getting a 32/64 bit value from the database */
382 int64_t value; /* value returned */
383 int count; /* number of values seen */
385 db_int64_ctx() : value(0), count(0) {};
388 db_int64_ctx(const db_int64_ctx&); /* prohibit pass by value */
389 db_int64_ctx &operator=(const db_int64_ctx&); /* prohibit class assignment */
392 /* Call back context for getting a list of comma separated strings from the
397 POOLMEM *list; /* list */
398 int count; /* number of values seen */
400 db_list_ctx() { list = get_pool_memory(PM_FNAME); reset(); }
401 ~db_list_ctx() { free_pool_memory(list); list = NULL; }
402 void reset() { *list = 0; count = 0;}
403 void add(const db_list_ctx &str) {
406 pm_strcat(list, ",");
408 pm_strcat(list, str.list);
412 void add(const char *str) {
414 pm_strcat(list, ",");
416 pm_strcat(list, str);
420 db_list_ctx(const db_list_ctx&); /* prohibit pass by value */
421 db_list_ctx &operator=(const db_list_ctx&); /* prohibit class assignment */
425 SQL_INTERFACE_TYPE_MYSQL = 0,
426 SQL_INTERFACE_TYPE_POSTGRESQL = 1,
427 SQL_INTERFACE_TYPE_SQLITE3 = 2,
428 SQL_INTERFACE_TYPE_INGRES = 3,
429 SQL_INTERFACE_TYPE_DBI = 4
434 SQL_TYPE_POSTGRESQL = 1,
435 SQL_TYPE_SQLITE3 = 2,
437 SQL_TYPE_UNKNOWN = 99
440 typedef void (DB_LIST_HANDLER)(void *, const char *);
441 typedef int (DB_RESULT_HANDLER)(void *, int, char **);
443 #define db_lock(mdb) mdb->_db_lock(__FILE__, __LINE__)
444 #define db_unlock(mdb) mdb->_db_unlock(__FILE__, __LINE__)
446 /* Current database version number for all drivers */
447 #define BDB_VERSION 14
449 class B_DB: public SMARTALLOC {
451 brwlock_t m_lock; /* transaction lock */
452 dlink m_link; /* queue control */
453 SQL_INTERFACETYPE m_db_interface_type; /* type of backend used */
454 SQL_DBTYPE m_db_type; /* database type */
455 int m_ref_count; /* reference count */
456 bool m_connected; /* connection made to db */
457 bool m_have_batch_insert; /* have batch insert support ? */
458 char *m_db_driver; /* database driver */
459 char *m_db_driverdir; /* database driver dir */
460 char *m_db_name; /* database name */
461 char *m_db_user; /* database user */
462 char *m_db_address; /* host name address */
463 char *m_db_socket; /* socket for local access */
464 char *m_db_password; /* database password */
465 int m_db_port; /* port for host name address */
466 bool m_disabled_batch_insert; /* explicitly disabled batch insert mode ? */
467 bool m_dedicated; /* is this connection dedicated? */
470 POOLMEM *errmsg; /* nicely edited error message */
471 POOLMEM *cmd; /* SQL command string */
472 POOLMEM *cached_path; /* cached path name */
473 int cached_path_len; /* length of cached path */
474 uint32_t cached_path_id; /* cached path id */
475 int changes; /* changes during transaction */
476 POOLMEM *fname; /* Filename only */
477 POOLMEM *path; /* Path only */
478 POOLMEM *esc_name; /* Escaped file name */
479 POOLMEM *esc_path; /* Escaped path name */
480 POOLMEM *esc_obj; /* Escaped restore object */
481 int fnl; /* file name length */
482 int pnl; /* path name length */
487 const char *get_db_name(void) { return m_db_name; };
488 const char *get_db_user(void) { return m_db_user; };
489 bool is_connected(void) { return m_connected; };
490 bool batch_insert_available(void) { return m_have_batch_insert; };
491 void increment_refcount(void) { m_ref_count++; };
493 /* low level methods */
494 bool db_match_database(const char *db_driver, const char *db_name,
495 const char *db_address, int db_port);
496 B_DB *db_clone_database_connection(JCR *jcr, bool mult_db_connections);
497 int db_get_type_index(void) { return m_db_type; };
498 const char *db_get_type(void);
499 void _db_lock(const char *file, int line);
500 void _db_unlock(const char *file, int line);
501 bool db_sql_query(const char *query, int flags=0);
502 void print_lock_info(FILE *fp);
504 /* Pure virtual low level methods */
505 virtual bool db_open_database(JCR *jcr) = 0;
506 virtual void db_close_database(JCR *jcr) = 0;
507 virtual void db_thread_cleanup(void) = 0;
508 virtual void db_escape_string(JCR *jcr, char *snew, char *old, int len) = 0;
509 virtual char *db_escape_object(JCR *jcr, char *old, int len) = 0;
510 virtual void db_unescape_object(JCR *jcr, char *from, int32_t expected_len,
511 POOLMEM **dest, int32_t *len) = 0;
512 virtual void db_start_transaction(JCR *jcr) = 0;
513 virtual void db_end_transaction(JCR *jcr) = 0;
514 virtual bool db_sql_query(const char *query, DB_RESULT_HANDLER *result_handler, void *ctx) = 0;
516 /* By default, we use db_sql_query */
517 virtual bool db_big_sql_query(const char *query,
518 DB_RESULT_HANDLER *result_handler, void *ctx) {
519 return db_sql_query(query, result_handler, ctx);
523 /* sql_query Query Flags */
524 #define QF_STORE_RESULT 0x01
526 /* Use for better error location printing */
527 #define UPDATE_DB(jcr, db, cmd) UpdateDB(__FILE__, __LINE__, jcr, db, cmd)
528 #define INSERT_DB(jcr, db, cmd) InsertDB(__FILE__, __LINE__, jcr, db, cmd)
529 #define QUERY_DB(jcr, db, cmd) QueryDB(__FILE__, __LINE__, jcr, db, cmd)
530 #define DELETE_DB(jcr, db, cmd) DeleteDB(__FILE__, __LINE__, jcr, db, cmd)
534 #include "sql_cmds.h"
536 /* Object used in db_list_xxx function */
539 char line[256]; /* Used to print last dash line */
542 e_list_type type; /* Vertical/Horizontal */
543 DB_LIST_HANDLER *send; /* send data back */
544 bool once; /* Used to print header one time */
545 void *ctx; /* send() user argument */
560 LIST_CTX(JCR *j, B_DB *m, DB_LIST_HANDLER *h, void *c, e_list_type t) {
573 * Some functions exported by sql.c for use within the cats directory.
575 int list_result(void *vctx, int cols, char **row);
576 int list_result(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *send, void *ctx, e_list_type type);
577 void list_dashes(B_DB *mdb, DB_LIST_HANDLER *send, void *ctx);
578 int get_sql_record_max(JCR *jcr, B_DB *mdb);
579 bool check_tables_version(JCR *jcr, B_DB *mdb);
580 bool db_check_max_connections(JCR *jcr, B_DB *mdb, uint32_t nb);
582 void print_dashes(B_DB *mdb);
583 void print_result(B_DB *mdb);
584 int QueryDB(const char *file, int line, JCR *jcr, B_DB *db, char *select_cmd);
585 int InsertDB(const char *file, int line, JCR *jcr, B_DB *db, char *select_cmd);
586 int DeleteDB(const char *file, int line, JCR *jcr, B_DB *db, char *delete_cmd);
587 int UpdateDB(const char *file, int line, JCR *jcr, B_DB *db, char *update_cmd);
588 void split_path_and_file(JCR *jcr, B_DB *mdb, const char *fname);
589 #endif /* __CATS_H_ */