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 */
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 */
286 DBId_t MediaId; /* Unique volume id */
287 char VolumeName[MAX_NAME_LENGTH]; /* Volume name */
288 char MediaType[MAX_NAME_LENGTH]; /* Media type */
289 DBId_t PoolId; /* Pool id */
290 time_t FirstWritten; /* Time Volume first written this usage */
291 time_t LastWritten; /* Time Volume last written */
292 time_t LabelDate; /* Date/Time Volume labeled */
293 time_t InitialWrite; /* Date/Time Volume first written */
294 int32_t LabelType; /* Label (Bacula/ANSI/IBM) */
295 uint32_t VolJobs; /* number of jobs on this medium */
296 uint32_t VolFiles; /* Number of files */
297 uint32_t VolBlocks; /* Number of blocks */
298 uint32_t VolMounts; /* Number of times mounted */
299 uint32_t VolErrors; /* Number of read/write errors */
300 uint32_t VolWrites; /* Number of writes */
301 uint32_t VolReads; /* Number of reads */
302 uint64_t VolBytes; /* Number of bytes written */
303 uint32_t VolParts; /* Number of parts written */
304 uint64_t MaxVolBytes; /* Max bytes to write to Volume */
305 uint64_t VolCapacityBytes; /* capacity estimate */
306 uint64_t VolReadTime; /* time spent reading volume */
307 uint64_t VolWriteTime; /* time spent writing volume */
308 utime_t VolRetention; /* Volume retention in seconds */
309 utime_t VolUseDuration; /* time in secs volume can be used */
310 uint32_t ActionOnPurge; /* action on purge, e.g. truncate the disk volume */
311 uint32_t MaxVolJobs; /* Max Jobs on Volume */
312 uint32_t MaxVolFiles; /* Max files on Volume */
313 int32_t Recycle; /* recycle yes/no */
314 int32_t Slot; /* slot in changer */
315 int32_t Enabled; /* 0=disabled, 1=enabled, 2=archived */
316 int32_t InChanger; /* Volume currently in changer */
317 DBId_t StorageId; /* Storage record Id */
318 uint32_t EndFile; /* Last file on volume */
319 uint32_t EndBlock; /* Last block on volume */
320 uint32_t RecycleCount; /* Number of times recycled */
321 char VolStatus[20]; /* Volume status */
322 DBId_t DeviceId; /* Device where Vol last written */
323 DBId_t LocationId; /* Where Volume is -- user defined */
324 DBId_t ScratchPoolId; /* Where to move if scratch */
325 DBId_t RecyclePoolId; /* Where to move when recycled */
326 /* Extra stuff not in DB */
327 faddr_t rec_addr; /* found record address */
328 /* Since the database returns times as strings, this is how we pass
331 char cFirstWritten[MAX_TIME_LENGTH]; /* FirstWritten returned from DB */
332 char cLastWritten[MAX_TIME_LENGTH]; /* LastWritten returned from DB */
333 char cLabelDate[MAX_TIME_LENGTH]; /* LabelData returned from DB */
334 char cInitialWrite[MAX_TIME_LENGTH]; /* InitialWrite returned from DB */
335 bool set_first_written;
339 /* Client record -- same as the database */
341 DBId_t ClientId; /* Unique Client id */
343 utime_t FileRetention;
344 utime_t JobRetention;
345 char Name[MAX_NAME_LENGTH]; /* Client name */
346 char Uname[256]; /* Uname for client */
349 /* Counter record as in database */
351 char Counter[MAX_NAME_LENGTH];
354 int32_t CurrentValue;
355 char WrapCounter[MAX_NAME_LENGTH];
359 /* FileSet record -- same as the database */
361 DBId_t FileSetId; /* Unique FileSet id */
362 char FileSet[MAX_NAME_LENGTH]; /* FileSet name */
363 char MD5[50]; /* MD5 signature of include/exclude */
364 time_t CreateTime; /* date created */
366 * This is where we return CreateTime
368 char cCreateTime[MAX_TIME_LENGTH]; /* CreateTime as returned from DB */
369 /* Not in DB but returned by db_create_fileset() */
370 bool created; /* set when record newly created */
373 /* Call back context for getting a 32/64 bit value from the database */
376 int64_t value; /* value returned */
377 int count; /* number of values seen */
379 db_int64_ctx() : value(0), count(0) {};
382 db_int64_ctx(const db_int64_ctx&); /* prohibit pass by value */
383 db_int64_ctx &operator=(const db_int64_ctx&); /* prohibit class assignment */
386 /* Call back context for getting a list of comma separated strings from the
391 POOLMEM *list; /* list */
392 int count; /* number of values seen */
394 db_list_ctx() { list = get_pool_memory(PM_FNAME); reset(); }
395 ~db_list_ctx() { free_pool_memory(list); list = NULL; }
396 void reset() { *list = 0; count = 0;}
397 void add(const db_list_ctx &str) {
400 pm_strcat(list, ",");
402 pm_strcat(list, str.list);
406 void add(const char *str) {
408 pm_strcat(list, ",");
410 pm_strcat(list, str);
414 db_list_ctx(const db_list_ctx&); /* prohibit pass by value */
415 db_list_ctx &operator=(const db_list_ctx&); /* prohibit class assignment */
419 SQL_INTERFACE_TYPE_MYSQL = 0,
420 SQL_INTERFACE_TYPE_POSTGRESQL = 1,
421 SQL_INTERFACE_TYPE_SQLITE3 = 2,
422 SQL_INTERFACE_TYPE_INGRES = 3,
423 SQL_INTERFACE_TYPE_DBI = 4
428 SQL_TYPE_POSTGRESQL = 1,
429 SQL_TYPE_SQLITE3 = 2,
431 SQL_TYPE_UNKNOWN = 99
434 typedef void (DB_LIST_HANDLER)(void *, const char *);
435 typedef int (DB_RESULT_HANDLER)(void *, int, char **);
437 #define db_lock(mdb) mdb->_db_lock(__FILE__, __LINE__)
438 #define db_unlock(mdb) mdb->_db_unlock(__FILE__, __LINE__)
440 /* Current database version number for all drivers */
441 #define BDB_VERSION 14
443 class B_DB: public SMARTALLOC {
445 brwlock_t m_lock; /* transaction lock */
446 dlink m_link; /* queue control */
447 SQL_INTERFACETYPE m_db_interface_type; /* type of backend used */
448 SQL_DBTYPE m_db_type; /* database type */
449 int m_ref_count; /* reference count */
450 bool m_connected; /* connection made to db */
451 bool m_have_batch_insert; /* have batch insert support ? */
452 char *m_db_driver; /* database driver */
453 char *m_db_driverdir; /* database driver dir */
454 char *m_db_name; /* database name */
455 char *m_db_user; /* database user */
456 char *m_db_address; /* host name address */
457 char *m_db_socket; /* socket for local access */
458 char *m_db_password; /* database password */
459 int m_db_port; /* port for host name address */
460 bool m_disabled_batch_insert; /* explicitly disabled batch insert mode ? */
463 POOLMEM *errmsg; /* nicely edited error message */
464 POOLMEM *cmd; /* SQL command string */
465 POOLMEM *cached_path; /* cached path name */
466 int cached_path_len; /* length of cached path */
467 uint32_t cached_path_id; /* cached path id */
468 int changes; /* changes during transaction */
469 POOLMEM *fname; /* Filename only */
470 POOLMEM *path; /* Path only */
471 POOLMEM *esc_name; /* Escaped file name */
472 POOLMEM *esc_path; /* Escaped path name */
473 POOLMEM *esc_obj; /* Escaped restore object */
474 int fnl; /* file name length */
475 int pnl; /* path name length */
480 const char *get_db_name(void) { return m_db_name; };
481 const char *get_db_user(void) { return m_db_user; };
482 bool is_connected(void) { return m_connected; };
483 bool batch_insert_available(void) { return m_have_batch_insert; };
484 void increment_refcount(void) { m_ref_count++; };
486 /* low level methods */
487 bool db_match_database(const char *db_driver, const char *db_name,
488 const char *db_address, int db_port);
489 B_DB *db_clone_database_connection(JCR *jcr, bool mult_db_connections);
490 int db_get_type_index(void) { return m_db_type; };
491 const char *db_get_type(void);
492 void _db_lock(const char *file, int line);
493 void _db_unlock(const char *file, int line);
494 bool db_sql_query(const char *query, int flags=0);
495 void print_lock_info(FILE *fp);
497 /* Pure virtual low level methods */
498 virtual bool db_open_database(JCR *jcr) = 0;
499 virtual void db_close_database(JCR *jcr) = 0;
500 virtual void db_thread_cleanup(void) = 0;
501 virtual void db_escape_string(JCR *jcr, char *snew, char *old, int len) = 0;
502 virtual char *db_escape_object(JCR *jcr, char *old, int len) = 0;
503 virtual void db_unescape_object(JCR *jcr, char *from, int32_t expected_len,
504 POOLMEM **dest, int32_t *len) = 0;
505 virtual void db_start_transaction(JCR *jcr) = 0;
506 virtual void db_end_transaction(JCR *jcr) = 0;
507 virtual bool db_sql_query(const char *query, DB_RESULT_HANDLER *result_handler, void *ctx) = 0;
509 /* By default, we use db_sql_query */
510 virtual bool db_big_sql_query(const char *query,
511 DB_RESULT_HANDLER *result_handler, void *ctx) {
512 return db_sql_query(query, result_handler, ctx);
516 /* sql_query Query Flags */
517 #define QF_STORE_RESULT 0x01
519 /* Use for better error location printing */
520 #define UPDATE_DB(jcr, db, cmd) UpdateDB(__FILE__, __LINE__, jcr, db, cmd)
521 #define INSERT_DB(jcr, db, cmd) InsertDB(__FILE__, __LINE__, jcr, db, cmd)
522 #define QUERY_DB(jcr, db, cmd) QueryDB(__FILE__, __LINE__, jcr, db, cmd)
523 #define DELETE_DB(jcr, db, cmd) DeleteDB(__FILE__, __LINE__, jcr, db, cmd)
527 #include "sql_cmds.h"
529 /* Object used in db_list_xxx function */
532 char line[256]; /* Used to print last dash line */
535 e_list_type type; /* Vertical/Horizontal */
536 DB_LIST_HANDLER *send; /* send data back */
537 bool once; /* Used to print header one time */
538 void *ctx; /* send() user argument */
553 LIST_CTX(JCR *j, B_DB *m, DB_LIST_HANDLER *h, void *c, e_list_type t) {
566 * Some functions exported by sql.c for use within the cats directory.
568 int list_result(void *vctx, int cols, char **row);
569 int list_result(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *send, void *ctx, e_list_type type);
570 void list_dashes(B_DB *mdb, DB_LIST_HANDLER *send, void *ctx);
571 int get_sql_record_max(JCR *jcr, B_DB *mdb);
572 bool check_tables_version(JCR *jcr, B_DB *mdb);
573 bool db_check_max_connections(JCR *jcr, B_DB *mdb, uint32_t nb);
575 void print_dashes(B_DB *mdb);
576 void print_result(B_DB *mdb);
577 int QueryDB(const char *file, int line, JCR *jcr, B_DB *db, char *select_cmd);
578 int InsertDB(const char *file, int line, JCR *jcr, B_DB *db, char *select_cmd);
579 int DeleteDB(const char *file, int line, JCR *jcr, B_DB *db, char *delete_cmd);
580 int UpdateDB(const char *file, int line, JCR *jcr, B_DB *db, char *update_cmd);
581 void split_path_and_file(JCR *jcr, B_DB *mdb, const char *fname);
582 #endif /* __CATS_H_ */