6 * Anyone who accesses the database will need to include
9 * This file contains definitions common to sql.c and
10 * the external world, and definitions destined only
11 * for the external world. This is control with
12 * the define __SQL_C, which is defined only in sql.c
18 Copyright (C) 2000-2004 Kern Sibbald and John Walker
20 This program is free software; you can redistribute it and/or
21 modify it under the terms of the GNU General Public License as
22 published by the Free Software Foundation; either version 2 of
23 the License, or (at your option) any later version.
25 This program is distributed in the hope that it will be useful,
26 but WITHOUT ANY WARRANTY; without even the implied warranty of
27 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
28 General Public License for more details.
30 You should have received a copy of the GNU General Public
31 License along with this program; if not, write to the Free
32 Software Foundation, Inc., 59 Temple Place - Suite 330, Boston,
41 typedef void (DB_LIST_HANDLER)(void *, const char *);
42 typedef int (DB_RESULT_HANDLER)(void *, int, char **);
44 #define db_lock(mdb) _db_lock(__FILE__, __LINE__, mdb)
45 #define db_unlock(mdb) _db_unlock(__FILE__, __LINE__, mdb)
52 #define sqlite_last_insert_rowid sqlite3_last_insert_rowid
53 #define sqlite_open sqlite3_open
54 #define sqlite_close sqlite3_close
55 #define sqlite_result sqlite3_result
56 #define sqlite_exec sqlite3_exec
57 #define sqlite_get_table sqlite3_get_table
58 #define sqlite_free_table sqlite3_free_table
65 /* Define opaque structure for sqlite */
70 #define IS_NUM(x) ((x) == 1)
71 #define IS_NOT_NULL(x) ((x) == 1)
73 typedef struct s_sql_field {
74 char *name; /* name of column */
75 int length; /* length */
76 int max_length; /* max length */
77 uint32_t type; /* type */
78 uint32_t flags; /* flags */
82 * This is the "real" definition that should only be
83 * used inside sql.c and associated database interface
88 BQUEUE bq; /* queue control */
89 brwlock_t lock; /* transaction lock */
93 int nrow; /* nrow returned from sqlite */
94 int ncolumn; /* ncolum returned from sqlite */
95 int num_rows; /* used by code */
96 int row; /* seek row */
97 int field; /* seek field */
98 SQL_FIELD **fields; /* defined fields */
102 char *db_address; /* host name address */
103 char *db_socket; /* socket for local access */
105 int db_port; /* port for host name address */
106 bool connected; /* connection made to db */
107 bool have_insert_id; /* do not have insert id */
108 bool fields_defined; /* set when fields defined */
109 char *sqlite_errmsg; /* error message returned by sqlite */
110 POOLMEM *errmsg; /* nicely edited error message */
111 POOLMEM *cmd; /* SQL command string */
112 POOLMEM *cached_path; /* cached path name */
113 int cached_path_len; /* length of cached path */
114 uint32_t cached_path_id; /* cached path id */
115 bool allow_transactions; /* transactions allowed */
116 bool transaction; /* transaction started */
117 int changes; /* changes during transaction */
118 POOLMEM *fname; /* Filename only */
119 POOLMEM *path; /* Path only */
120 POOLMEM *esc_name; /* Escaped file/path name */
121 int fnl; /* file name length */
122 int pnl; /* path name length */
127 * "Generic" names for easier conversion
131 #define sql_store_result(x) (x)->result
132 #define sql_free_result(x) my_sqlite_free_table(x)
133 #define sql_fetch_row(x) my_sqlite_fetch_row(x)
134 #define sql_query(x, y) my_sqlite_query((x), (y))
136 #define sql_insert_id(x,y) sqlite3_last_insert_rowid((x)->db)
137 #define sql_close(x) sqlite3_close((x)->db)
139 #define sql_insert_id(x,y) sqlite_last_insert_rowid((x)->db)
140 #define sql_close(x) sqlite_close((x)->db)
142 #define sql_strerror(x) (x)->sqlite_errmsg?(x)->sqlite_errmsg:"unknown"
143 #define sql_num_rows(x) (x)->nrow
144 #define sql_data_seek(x, i) (x)->row = (i)
145 #define sql_affected_rows(x) 1
146 #define sql_field_seek(x, y) my_sqlite_field_seek((x), (y))
147 #define sql_fetch_field(x) my_sqlite_fetch_field(x)
148 #define sql_num_fields(x) ((x)->ncolumn)
149 #define SQL_ROW char**
153 /* In cats/sqlite.c */
154 void my_sqlite_free_table(B_DB *mdb);
155 SQL_ROW my_sqlite_fetch_row(B_DB *mdb);
156 int my_sqlite_query(B_DB *mdb, char *cmd);
157 void my_sqlite_field_seek(B_DB *mdb, int field);
158 SQL_FIELD *my_sqlite_fetch_field(B_DB *mdb);
165 #define BDB_VERSION 8
170 * This is the "real" definition that should only be
171 * used inside sql.c and associated database interface
176 typedef struct s_db {
177 BQUEUE bq; /* queue control */
178 brwlock_t lock; /* transaction lock */
183 my_ulonglong num_rows;
188 char *db_address; /* host address */
189 char *db_socket; /* socket for local access */
190 int db_port; /* port of host address */
191 int have_insert_id; /* do have insert_id() */
193 POOLMEM *errmsg; /* nicely edited error message */
194 POOLMEM *cmd; /* SQL command string */
195 POOLMEM *cached_path;
196 int cached_path_len; /* length of cached path */
197 uint32_t cached_path_id;
198 int changes; /* changes made to db */
199 POOLMEM *fname; /* Filename only */
200 POOLMEM *path; /* Path only */
201 POOLMEM *esc_name; /* Escaped file/path name */
202 int fnl; /* file name length */
203 int pnl; /* path name length */
206 #define DB_STATUS int
208 /* "Generic" names for easier conversion */
209 #define sql_store_result(x) mysql_store_result((x)->db)
210 #define sql_free_result(x) mysql_free_result((x)->result)
211 #define sql_fetch_row(x) mysql_fetch_row((x)->result)
212 #define sql_query(x, y) mysql_query((x)->db, (y))
213 #define sql_close(x) mysql_close((x)->db)
214 #define sql_strerror(x) mysql_error((x)->db)
215 #define sql_num_rows(x) mysql_num_rows((x)->result)
216 #define sql_data_seek(x, i) mysql_data_seek((x)->result, (i))
217 #define sql_affected_rows(x) mysql_affected_rows((x)->db)
218 #define sql_insert_id(x,y) mysql_insert_id((x)->db)
219 #define sql_field_seek(x, y) mysql_field_seek((x)->result, (y))
220 #define sql_fetch_field(x) mysql_fetch_field((x)->result)
221 #define sql_num_fields(x) (int)mysql_num_fields((x)->result)
222 #define SQL_ROW MYSQL_ROW
223 #define SQL_FIELD MYSQL_FIELD
227 #ifdef HAVE_POSTGRESQL
229 #define BDB_VERSION 8
231 #include <libpq-fe.h>
233 /* TEMP: the following is taken from select OID, typname from pg_type; */
234 #define IS_NUM(x) ((x) == 20 || (x) == 21 || (x) == 23 || (x) == 700 || (x) == 701)
235 #define IS_NOT_NULL(x) ((x) == 1)
237 typedef char **POSTGRESQL_ROW;
238 typedef struct pg_field {
242 unsigned int flags; // 1 == not null
247 * This is the "real" definition that should only be
248 * used inside sql.c and associated database interface
251 * P O S T G R E S Q L
253 typedef struct s_db {
254 BQUEUE bq; /* queue control */
255 brwlock_t lock; /* transaction lock */
260 POSTGRESQL_FIELD *fields;
263 int row_number; /* what row number did we get via my_postgresql_data_seek? */
264 int field_number; /* what field number did we get via my_postgresql_field_seek? */
269 char *db_address; /* host address */
270 char *db_socket; /* socket for local access */
271 int db_port; /* port of host address */
272 int have_insert_id; /* do have insert_id() */
274 POOLMEM *errmsg; /* nicely edited error message */
275 POOLMEM *cmd; /* SQL command string */
276 POOLMEM *cached_path;
277 int cached_path_len; /* length of cached path */
278 uint32_t cached_path_id;
279 bool allow_transactions; /* transactions allowed */
280 bool transaction; /* transaction started */
281 int changes; /* changes made to db */
282 POOLMEM *fname; /* Filename only */
283 POOLMEM *path; /* Path only */
284 POOLMEM *esc_name; /* Escaped file/path name */
285 int fnl; /* file name length */
286 int pnl; /* path name length */
289 void my_postgresql_free_result(B_DB *mdb);
290 POSTGRESQL_ROW my_postgresql_fetch_row (B_DB *mdb);
291 int my_postgresql_query (B_DB *mdb, const char *query);
292 void my_postgresql_data_seek (B_DB *mdb, int row);
293 int my_postgresql_currval (B_DB *mdb, char *table_name);
294 void my_postgresql_field_seek (B_DB *mdb, int row);
295 POSTGRESQL_FIELD * my_postgresql_fetch_field(B_DB *mdb);
298 /* "Generic" names for easier conversion */
299 #define sql_store_result(x) ((x)->result)
300 #define sql_free_result(x) my_postgresql_free_result(x)
301 #define sql_fetch_row(x) my_postgresql_fetch_row(x)
302 #define sql_query(x, y) my_postgresql_query((x), (y))
303 #define sql_close(x) PQfinish((x)->db)
304 #define sql_strerror(x) PQresultErrorMessage((x)->result)
305 #define sql_num_rows(x) ((unsigned) PQntuples((x)->result))
306 #define sql_data_seek(x, i) my_postgresql_data_seek((x), (i))
307 #define sql_affected_rows(x) ((unsigned) atoi(PQcmdTuples((x)->result)))
308 #define sql_insert_id(x,y) my_postgresql_currval((x), (y))
309 #define sql_field_seek(x, y) my_postgresql_field_seek((x), (y))
310 #define sql_fetch_field(x) my_postgresql_fetch_field(x)
311 #define sql_num_fields(x) ((x)->num_fields)
312 #define SQL_ROW POSTGRESQL_ROW
313 #define SQL_FIELD POSTGRESQL_FIELD
315 #else /* USE BACULA DB routines */
317 #define HAVE_BACULA_DB 1
319 /* Change this each time there is some incompatible
320 * file format change!!!!
322 #define BDB_VERSION 13 /* file version number */
325 int bdb_version; /* Version number */
326 uint32_t JobId; /* next Job Id */
327 uint32_t PoolId; /* next Pool Id */
328 uint32_t MediaId; /* next Media Id */
329 uint32_t JobMediaId; /* next JobMedia Id */
330 uint32_t ClientId; /* next Client Id */
331 uint32_t FileSetId; /* nest FileSet Id */
332 time_t time; /* time file written */
336 /* This is the REAL definition for using the
339 typedef struct s_db {
340 BQUEUE bq; /* queue control */
341 /* pthread_mutex_t mutex; */ /* single thread lock */
342 brwlock_t lock; /* transaction lock */
343 int ref_count; /* number of times opened */
344 struct s_control control; /* control file structure */
345 int cfd; /* control file device */
346 FILE *jobfd; /* Jobs records file descriptor */
347 FILE *poolfd; /* Pool records fd */
348 FILE *mediafd; /* Media records fd */
349 FILE *jobmediafd; /* JobMedia records fd */
350 FILE *clientfd; /* Client records fd */
351 FILE *filesetfd; /* FileSet records fd */
352 char *db_name; /* name of database */
353 POOLMEM *errmsg; /* nicely edited error message */
354 POOLMEM *cmd; /* Command string */
355 POOLMEM *cached_path;
356 int cached_path_len; /* length of cached path */
357 uint32_t cached_path_id;
360 #endif /* HAVE_MYSQL */
361 #endif /* HAVE_SQLITE */
362 #endif /* HAVE_POSTGRESQL */
364 /* Use for better error location printing */
365 #define UPDATE_DB(jcr, db, cmd) UpdateDB(__FILE__, __LINE__, jcr, db, cmd)
366 #define INSERT_DB(jcr, db, cmd) InsertDB(__FILE__, __LINE__, jcr, db, cmd)
367 #define QUERY_DB(jcr, db, cmd) QueryDB(__FILE__, __LINE__, jcr, db, cmd)
368 #define DELETE_DB(jcr, db, cmd) DeleteDB(__FILE__, __LINE__, jcr, db, cmd)
371 #else /* not __SQL_C */
373 /* This is a "dummy" definition for use outside of sql.c
375 typedef struct s_db {
376 int dummy; /* for SunOS compiler */
381 extern uint32_t bacula_db_version;
383 /* ***FIXME*** FileId_t should *really* be uint64_t
384 * but at the current time, this breaks MySQL.
386 typedef uint32_t FileId_t;
387 typedef uint32_t DBId_t; /* general DB id type */
388 typedef uint32_t JobId_t;
393 /* Job information passed to create job record and update
394 * job record at end of job. Note, although this record
395 * contains all the fields found in the Job database record,
396 * it also contains fields found in the JobMedia record.
401 char Job[MAX_NAME_LENGTH]; /* Job unique name */
402 char Name[MAX_NAME_LENGTH]; /* Job base name */
403 int JobType; /* actually char(1) */
404 int JobLevel; /* actually char(1) */
405 int JobStatus; /* actually char(1) */
406 DBId_t ClientId; /* Id of client */
407 DBId_t PoolId; /* Id of pool */
408 DBId_t FileSetId; /* Id of FileSet */
409 time_t SchedTime; /* Time job scheduled */
410 time_t StartTime; /* Job start time */
411 time_t EndTime; /* Job termination time */
412 utime_t JobTDate; /* Backup time/date in seconds */
413 uint32_t VolSessionId;
414 uint32_t VolSessionTime;
417 uint32_t JobMissingFiles;
420 /* Note, FirstIndex, LastIndex, Start/End File and Block
421 * are only used in the JobMedia record.
423 uint32_t FirstIndex; /* First index this Volume */
424 uint32_t LastIndex; /* Last index this Volume */
430 char cSchedTime[MAX_TIME_LENGTH];
431 char cStartTime[MAX_TIME_LENGTH];
432 char cEndTime[MAX_TIME_LENGTH];
433 /* Extra stuff not in DB */
437 /* Job Media information used to create the media records
438 * for each Volume used for the job.
440 /* JobMedia record */
441 struct JOBMEDIA_DBR {
442 DBId_t JobMediaId; /* record id */
443 JobId_t JobId; /* JobId */
444 DBId_t MediaId; /* MediaId */
445 uint32_t FirstIndex; /* First index this Volume */
446 uint32_t LastIndex; /* Last index this Volume */
447 uint32_t StartFile; /* File for start of data */
448 uint32_t EndFile; /* End file on Volume */
449 uint32_t StartBlock; /* start block on tape */
450 uint32_t EndBlock; /* last block */
454 /* Volume Parameter structure */
456 char VolumeName[MAX_NAME_LENGTH]; /* Volume name */
457 uint32_t VolIndex; /* Volume seqence no. */
458 uint32_t FirstIndex; /* First index this Volume */
459 uint32_t LastIndex; /* Last index this Volume */
460 uint32_t StartFile; /* File for start of data */
461 uint32_t EndFile; /* End file on Volume */
462 uint32_t StartBlock; /* start block on tape */
463 uint32_t EndBlock; /* last block */
467 /* Attributes record -- NOT same as in database because
468 * in general, this "record" creates multiple database
469 * records (e.g. pathname, filename, fileattributes).
472 char *fname; /* full path & filename */
473 char *link; /* link if any */
474 char *attr; /* attributes statp */
485 /* File record -- same format as database */
496 int SigType; /* NO_SIG/MD5_SIG/SHA1_SIG */
499 /* Pool record -- same format as database */
502 char Name[MAX_NAME_LENGTH]; /* Pool name */
503 uint32_t NumVols; /* total number of volumes */
504 uint32_t MaxVols; /* max allowed volumes */
505 int32_t UseOnce; /* set to use once only */
506 int32_t UseCatalog; /* set to use catalog */
507 int32_t AcceptAnyVolume; /* set to accept any volume sequence */
508 int32_t AutoPrune; /* set to prune automatically */
509 int32_t Recycle; /* default Vol recycle flag */
510 utime_t VolRetention; /* retention period in seconds */
511 utime_t VolUseDuration; /* time in secs volume can be used */
512 uint32_t MaxVolJobs; /* Max Jobs on Volume */
513 uint32_t MaxVolFiles; /* Max files on Volume */
514 uint64_t MaxVolBytes; /* Max bytes on Volume */
515 char PoolType[MAX_NAME_LENGTH];
516 char LabelFormat[MAX_NAME_LENGTH];
517 /* Extra stuff not in DB */
521 /* Media record -- same as the database */
523 DBId_t MediaId; /* Unique volume id */
524 char VolumeName[MAX_NAME_LENGTH]; /* Volume name */
525 char MediaType[MAX_NAME_LENGTH]; /* Media type */
526 DBId_t PoolId; /* Pool id */
527 time_t FirstWritten; /* Time Volume first written */
528 time_t LastWritten; /* Time Volume last written */
529 time_t LabelDate; /* Date/Time Volume labeled */
530 uint32_t VolJobs; /* number of jobs on this medium */
531 uint32_t VolFiles; /* Number of files */
532 uint32_t VolBlocks; /* Number of blocks */
533 uint32_t VolMounts; /* Number of times mounted */
534 uint32_t VolErrors; /* Number of read/write errors */
535 uint32_t VolWrites; /* Number of writes */
536 uint32_t VolReads; /* Number of reads */
537 uint64_t VolBytes; /* Number of bytes written */
538 uint32_t VolParts; /* Number of parts written */
539 uint64_t MaxVolBytes; /* Max bytes to write to Volume */
540 uint64_t VolCapacityBytes; /* capacity estimate */
541 uint64_t VolReadTime; /* time spent reading volume */
542 uint64_t VolWriteTime; /* time spent writing volume */
543 utime_t VolRetention; /* Volume retention in seconds */
544 utime_t VolUseDuration; /* time in secs volume can be used */
545 uint32_t MaxVolJobs; /* Max Jobs on Volume */
546 uint32_t MaxVolFiles; /* Max files on Volume */
547 int32_t Recycle; /* recycle yes/no */
548 int32_t Slot; /* slot in changer */
549 int32_t InChanger; /* Volume currently in changer */
550 uint32_t EndFile; /* Last file on volume */
551 uint32_t EndBlock; /* Last block on volume */
552 char VolStatus[20]; /* Volume status */
553 /* Extra stuff not in DB */
554 faddr_t rec_addr; /* found record address */
555 /* Since the database returns times as strings, this is how we pass
558 char cFirstWritten[MAX_TIME_LENGTH]; /* FirstWritten returned from DB */
559 char cLastWritten[MAX_TIME_LENGTH]; /* LastWritten returned from DB */
560 char cLabelData[MAX_TIME_LENGTH]; /* LabelData returned from DB */
563 /* Client record -- same as the database */
565 DBId_t ClientId; /* Unique Client id */
567 utime_t FileRetention;
568 utime_t JobRetention;
569 char Name[MAX_NAME_LENGTH]; /* Client name */
570 char Uname[256]; /* Uname for client */
573 /* Counter record as in database */
575 char Counter[MAX_NAME_LENGTH];
578 int32_t CurrentValue;
579 char WrapCounter[MAX_NAME_LENGTH];
583 /* FileSet record -- same as the database */
585 DBId_t FileSetId; /* Unique FileSet id */
586 char FileSet[MAX_NAME_LENGTH]; /* FileSet name */
587 char MD5[50]; /* MD5 signature of include/exclude */
588 time_t CreateTime; /* date created */
590 * This is where we return CreateTime
592 char cCreateTime[MAX_TIME_LENGTH]; /* CreateTime as returned from DB */
593 /* Not in DB but returned by db_create_fileset() */
594 bool created; /* set when record newly created */
603 * Some functions exported by sql.c for use withing the
606 void list_result(B_DB *mdb, DB_LIST_HANDLER *send, void *ctx, e_list_type type);
607 void list_dashes(B_DB *mdb, DB_LIST_HANDLER *send, void *ctx);
608 int get_sql_record_max(JCR *jcr, B_DB *mdb);
609 int check_tables_version(JCR *jcr, B_DB *mdb);
610 void _db_unlock(const char *file, int line, B_DB *mdb);
611 void _db_lock(const char *file, int line, B_DB *mdb);
613 #endif /* __SQL_H_ */