Developers, misc and problems manuals

[bacula/docs] / docs / manuals / en / developers / catalog.tex

%%
%%

\chapter{Catalog Services}
\label{_ChapterStart30}
\index[general]{Services!Catalog }
\index[general]{Catalog Services }

\section{General}
\index[general]{General }
\addcontentsline{toc}{subsection}{General}

This chapter is intended to be a technical discussion of the Catalog services
and as such is not targeted at end users but rather at developers and system
administrators that want or need to know more of the working details of {\bf
Bacula}.

The {\bf Bacula Catalog} services consist of the programs that provide the SQL
database engine for storage and retrieval of all information concerning files
that were backed up and their locations on the storage media.

We have investigated the possibility of using the following SQL engines for
Bacula: Beagle, mSQL, GNU SQL, PostgreSQL, SQLite, Oracle, and MySQL. Each
presents certain problems with either licensing or maturity. At present, we
have chosen for development purposes to use MySQL, PostgreSQL and SQLite.
MySQL was chosen because it is fast, proven to be reliable, widely used, and
actively being developed. MySQL is released under the GNU GPL license.
PostgreSQL was chosen because it is a full-featured, very mature database, and
because Dan Langille did the Bacula driver for it. PostgreSQL is distributed
under the BSD license. SQLite was chosen because it is small, efficient, and
can be directly embedded in {\bf Bacula} thus requiring much less effort from
the system administrator or person building {\bf Bacula}. In our testing
SQLite has performed very well, and for the functions that we use, it has
never encountered any errors except that it does not appear to handle
databases larger than 2GBytes. That said, we would not recommend it for
serious production use.

The Bacula SQL code has been written in a manner that will allow it to be
easily modified to support any of the current SQL database systems on the
market (for example: mSQL, iODBC, unixODBC, Solid, OpenLink ODBC, EasySoft
ODBC, InterBase, Oracle8, Oracle7, and DB2).

If you do not specify either \lstinline+--with-mysql+ or \lstinline+--with-postgresq+ or
\lstinline+--with-sqlite+ on the ./configure line, Bacula will use its minimalist
internal database. This database is kept for build reasons but is no longer
supported. Bacula {\bf requires} one of the three databases (MySQL,
PostgreSQL, or SQLite) to run.

\subsection{Filenames and Maximum Filename Length}
\index[general]{Filenames and Maximum Filename Length }
\index[general]{Length!Filenames and Maximum Filename }
\addcontentsline{toc}{subsubsection}{Filenames and Maximum Filename Length}

In general, either MySQL, PostgreSQL or SQLite permit storing arbitrary long
path names and file names in the catalog database. In practice, there still
may be one or two places in the Catalog interface code that restrict the
maximum path length to 512 characters and the maximum file name length to 512
characters. These restrictions are believed to have been removed. Please note,
these restrictions apply only to the Catalog database and thus to your ability
to list online the files saved during any job. All information received and
stored by the Storage daemon (normally on tape) allows and handles arbitrarily
long path and filenames.

\subsection{Installing and Configuring MySQL}
\index[general]{MySQL!Installing and Configuring }
\index[general]{Installing and Configuring MySQL }
%\addcontentsline{toc}{subsubsection}{Installing and Configuring MySQL}

For the details of installing and configuring MySQL, please see the
\bsysxrlink{Installing and Configuring MySQL}{MySqlChapter}{main}{chapter} of
the \mainman{}.

\subsection{Installing and Configuring PostgreSQL}
\index[general]{PostgreSQL!Installing and Configuring }
\index[general]{Installing and Configuring PostgreSQL }
%\addcontentsline{toc}{subsubsection}{Installing and Configuring PostgreSQL}

For the details of installing and configuring PostgreSQL, please see the
\bsysxrlink{Installing and Configuring PostgreSQL}{PostgreSqlChapter}{main}{chapter}
 of the \mainman{}.

\subsection{Installing and Configuring SQLite}
\index[general]{Installing and Configuring SQLite }
\index[general]{SQLite!Installing and Configuring }
%\addcontentsline{toc}{subsubsection}{Installing and Configuring SQLite}

For the details of installing and configuring SQLite, please see the
\bsysxrlink{Installing and Configuring SQLite}{SqlLiteChapter}{main}{chapter} of
the \mainman{}.

\subsection{Internal Bacula Catalog}
\index[general]{Catalog!Internal Bacula }
\index[general]{Internal Bacula Catalog }
%\addcontentsline{toc}{subsubsection}{Internal Bacula Catalog}

Please see the \bsysxrlink{Internal Bacula Database}
{chap:InternalBaculaDatabase}{misc}{chapter} of the \miscman{} for more details.

\subsection{Database Table Design}
\index[general]{Design!Database Table }
\index[general]{Database Table Design }
%\addcontentsline{toc}{subsubsection}{Database Table Design}

All discussions that follow pertain to the MySQL database. The details for the
PostgreSQL and SQLite databases are essentially identical except for that all
fields in the SQLite database are stored as ASCII text and some of the
database creation statements are a bit different. The details of the internal
Bacula catalog are not discussed here.

Because the Catalog database may contain very large amounts of data for large
sites, we have made a modest attempt to normalize the data tables to reduce
redundant information. While reducing the size of the database significantly,
it does, unfortunately, add some complications to the structures.

In simple terms, the Catalog database must contain a record of all Jobs run by
Bacula, and for each Job, it must maintain a list of all files saved, with
their File Attributes (permissions, create date, ...), and the location and
Media on which the file is stored. This is seemingly a simple task, but it
represents a huge amount interlinked data. Note: the list of files and their
attributes is not maintained when using the internal Bacula database. The data
stored in the File records, which allows the user or administrator to obtain a
list of all files backed up during a job, is by far the largest volume of
information put into the Catalog database.

Although the Catalog database has been designed to handle backup data for
multiple clients, some users may want to maintain multiple databases, one for
each machine to be backed up. This reduces the risk of confusion of accidental
restoring a file to the wrong machine as well as reducing the amount of data
in a single database, thus increasing efficiency and reducing the impact of a
lost or damaged database.

\section{Sequence of Creation of Records for a Save Job}
\index[general]{Sequence of Creation of Records for a Save Job }
\index[general]{Job!Sequence of Creation of Records for a Save }
\addcontentsline{toc}{subsection}{Sequence of Creation of Records for a Save
Job}

Start with StartDate, ClientName, Filename, Path, Attributes, MediaName,
MediaCoordinates. (PartNumber, NumParts). In the steps below, ``Create new''
means to create a new record whether or not it is unique. ``Create unique''
means each record in the database should be unique. Thus, one must first
search to see if the record exists, and only if not should a new one be
created, otherwise the existing RecordId should be used.

\begin{enumerate}
\item Create new Job record with StartDate; save JobId
\item Create unique Media record; save MediaId
\item Create unique Client record; save ClientId
\item Create unique Filename record; save FilenameId
\item Create unique Path record; save PathId
\item Create unique Attribute record; save AttributeId
   store ClientId, FilenameId, PathId, and Attributes
\item Create new File record
   store JobId, AttributeId, MediaCoordinates, etc
\item Repeat steps 4 through 8 for each file
\item Create a JobMedia record; save MediaId
\item Update Job record filling in EndDate and other Job statistics
   \end{enumerate}

\section{Database Tables}
\index[general]{Database Tables }
\index[general]{Tables!Database }
%\addcontentsline{toc}{subsection}{Database Tables}
%\addcontentsline{lot}{table}{Filename Table Layout}
\LTXtable{\linewidth}{table_dbfilename}

The {\bf Filename} table \bsysref{table:dbfilename} contains the name of each file backed up
with the path removed. If different directories or machines contain the same
filename, only one copy will be saved in this table.

%\addcontentsline{lot}{table}{Path Table Layout}
\LTXtable{\linewidth}{table_dbpath}

The {\bf Path} table \bsysref{table:dbpath} contains the path or directory names of all
directories on the system or systems. The filename and any MSDOS disk name are
stripped off. As with the filename, only one copy of each directory name is
kept regardless of how many machines or drives have the same directory. These
path names should be stored in Unix path name format.

Some simple testing on a Linux file system indicates that separating the
filename and the path may be more complication than is warranted by the space
savings. For example, this system has a total of 89,097 files, 60,467 of which
have unique filenames, and there are 4,374 unique paths.

Finding all those files and doing two stats() per file takes an average wall
clock time of 1 min 35 seconds on a 400MHz machine running RedHat 6.1 Linux.

Finding all those files and putting them directly into a MySQL database with
the path and filename defined as TEXT, which is variable length up to 65,535
characters takes 19 mins 31 seconds and creates a 27.6 MByte database.

Doing the same thing, but inserting them into Blob fields with the filename
indexed on the first 30 characters and the path name indexed on the 255 (max)
characters takes 5 mins 18 seconds and creates a 5.24 MB database. Rerunning
the job (with the database already created) takes about 2 mins 50 seconds.

Running the same as the last one (Path and Filename Blob), but Filename
indexed on the first 30 characters and the Path on the first 50 characters
(linear search done there after) takes 5 mins on the average and creates a 3.4
MB database. Rerunning with the data already in the DB takes 3 mins 35
seconds.

Finally, saving only the full path name rather than splitting the path and the
file, and indexing it on the first 50 characters takes 6 mins 43 seconds and
creates a 7.35 MB database.


%\addcontentsline{lot}{table}
\LTXtable{\linewidth}{table_dbfile}

The {\bf File} table \bsysref{table:dbfile} contains one entry for each file backed up by
Bacula. Thus a file that is backed up multiple times (as is normal) will have
multiple entries in the File table. This will probably be the table with the
most number of records. Consequently, it is essential to keep the size of this
record to an absolute minimum. At the same time, this table must contain all
the information (or pointers to the information) about the file and where it
is backed up. Since a file may be backed up many times without having changed,
the path and filename are stored in separate tables.

This table contains by far the largest amount of information in the Catalog
database, both from the stand point of number of records, and the stand point
of total database size. As a consequence, the user must take care to
periodically reduce the number of File records using the {\bf retention}
command in the Console program.

%\addcontentsline{lot}{table}{Job Table Layout}
\LTXtable{\linewidth}{table_dbjob}

The {\bf Job} table \bsysref{table:dbjob} contains one record for each Job run by Bacula. Thus
normally, there will be one per day per machine added to the database. Note,
the JobId is used to index Job records in the database, and it often is shown
to the user in the Console program. However, care must be taken with its use
as it is not unique from database to database. For example, the user may have
a database for Client data saved on machine Rufus and another database for
Client data saved on machine Roxie. In this case, the two database will each
have JobIds that match those in another database. For a unique reference to a
Job, see Job below.

The Name field of the Job record corresponds to the Name resource record given
in the Director's configuration file. Thus it is a generic name, and it will
be normal to find many Jobs (or even all Jobs) with the same Name.

The Job field contains a combination of the Name and the schedule time of the
Job by the Director. Thus for a given Director, even with multiple Catalog
databases, the Job will contain a unique name that represents the Job.

For a given Storage daemon, the VolSessionId and VolSessionTime form a unique
identification of the Job. This will be the case even if multiple Directors
are using the same Storage daemon.

The Job Type (or simply Type) can have one of the following values:

%\addcontentsline{lot}{table}{Job Types}
\LTXtable{\linewidth}{table_dbjobtypes}
Note, the Job Type values in table \bsysref{table:dbjobtypes} noted above are not kept in an SQL table.


The JobStatus field specifies how the job terminated, and can be one of the
following:
\LTXtable{\linewidth}{table_dbjobstatuses}


%\addcontentsline{lot}{table}{File Sets Table Layout}
\LTXtable{\linewidth}{table_dbfileset}

The {\bf FileSet} table \bsysref{table:dbfileset} contains one entry for each FileSet that is used. The
MD5 signature is kept to ensure that if the user changes anything inside the
FileSet, it will be detected and the new FileSet will be used. This is
particularly important when doing an incremental update. If the user deletes a
file or adds a file, we need to ensure that a Full backup is done prior to the
next incremental.


%\addcontentsline{lot}{table}
\LTXtable{\linewidth}{table_dbjobmedia}

The {\bf JobMedia} table \bsysref{table:dbjobmedia} contains one entry at the following: start of
the job, start of each new tape file, start of each new tape, end of the
job.  Since by default, a new tape file is written every 2GB, in general,
you will have more than 2 JobMedia records per Job.  The number can be
varied by changing the "Maximum File Size" specified in the Device
resource.  This record allows Bacula to efficiently position close to
(within 2GB) any given file in a backup.  For restoring a full Job,
these records are not very important, but if you want to retrieve
a single file that was written near the end of a 100GB backup, the
JobMedia records can speed it up by orders of magnitude by permitting
forward spacing files and blocks rather than reading the whole 100GB
backup.




%\addcontentsline{lot}{table}{Media Table Layout}
\LTXtable{\linewidth}{table_dbmedia}

The {\bf Volume} table\footnote{Internally referred to as the Media table} \bsysref{table:dbmedia}  contains
one entry for each volume, that is each tape, cassette (8mm, DLT, DAT, ...),
or file on which information is or was backed up. There is one Volume record
created for each of the NumVols specified in the Pool resource record.

%\addcontentsline{lot}{table}{Pool Table Layout}
\LTXtable{\linewidth}{table_dbpool}

The {\bf Pool} table \bsysref{table:dbpool} contains one entry for each media pool controlled by
Bacula in this database. One media record exists for each of the NumVols
contained in the Pool. The PoolType is a Bacula defined keyword. The MediaType
is defined by the administrator, and corresponds to the MediaType specified in
the Director's Storage definition record. The CurrentVol is the sequence
number of the Media record for the current volume.


%\addcontentsline{lot}{table}{Client Table Layout}
\LTXtable{\linewidth}{table_dbclient}

The {\bf Client} table \bsysref{table:dbclient} contains one entry for each machine backed up by Bacula
in this database. Normally the Name is a fully qualified domain name.


%\addcontentsline{lot}{table}{Storage Table Layout}
\LTXtable{\linewidth}{table_dbstorage}

The {\bf Storage} table \bsysref{table:dbstorage} contains one entry for each Storage used.


%\addcontentsline{lot}{table}{Counter Table Layout}
\LTXtable{\linewidth}{table_dbcounter}

The {\bf Counter} table \bsysref{table:dbcounter} contains one entry for each permanent counter defined
by the user.

%\addcontentsline{lot}{table}{Job History Table Layout}
\LTXtable{\linewidth}{table_dbjobhistory}

The {\bf JobHisto} table \bsysref{table:dbjobhistory} is the same as the Job table, but it keeps
long term statistics (i.e. it is not pruned with the Job).


%\addcontentsline{lot}{table}{Log Table Layout}
\LTXtable{\linewidth}{table_dblog}

The {\bf Log} table \bsysref{table:dblog} contains a log of all Job output.

%\addcontentsline{lot}{table}{Location Table Layout}
\LTXtable{\linewidth}{table_dblocation}

The {\bf Location} table \bsysref{table:dblocation} defines where a Volume is physically.


%\addcontentsline{lot}{table}{Location Log Table Layout}
\LTXtable{\linewidth}{table_dblocationlog}

The {\bf Location Log} table \bsysref{table:dblocationlog} contains a log of all Job output.


%\addcontentsline{lot}{table}{Version Table Layout}
\LTXtable{\linewidth}{table_dbversion}

The {\bf Version} table \bsysref{table:dbversion} defines the Bacula database version number. Bacula
checks this number before reading the database to ensure that it is compatible
with the Bacula binary file.


%\addcontentsline{lot}{table}{Base Files Table Layout}
\LTXtable{\linewidth}{table_dbbasefiles}

The {\bf BaseFiles} table \bsysref{table:dbbasefiles} contains all the File references for a particular
JobId that point to a Base file -- i.e. they were previously saved and hence
were not saved in the current JobId but in BaseJobId under FileId. FileIndex
is the index of the file, and is used for optimization of Restore jobs to
prevent the need to read the FileId record when creating the in memory tree.
This record is not yet implemented.

\

\subsection{MySQL Table Definition}
\index[general]{MySQL Table Definition }
\index[general]{Definition!MySQL Table }
\addcontentsline{toc}{subsubsection}{MySQL Table Definition}

The commands used to create the MySQL tables are as follows:

\footnotesize
\begin{lstlisting}
USE bacula;
CREATE TABLE Filename (
  FilenameId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  Name BLOB NOT NULL,
  PRIMARY KEY(FilenameId),
  INDEX (Name(30))
  );
CREATE TABLE Path (
   PathId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
   Path BLOB NOT NULL,
   PRIMARY KEY(PathId),
   INDEX (Path(50))
   );
CREATE TABLE File (
   FileId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
   FileIndex INTEGER UNSIGNED NOT NULL DEFAULT 0,
   JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
   PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
   FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
   MarkId INTEGER UNSIGNED NOT NULL DEFAULT 0,
   LStat TINYBLOB NOT NULL,
   MD5 TINYBLOB NOT NULL,
   PRIMARY KEY(FileId),
   INDEX (JobId),
   INDEX (PathId),
   INDEX (FilenameId)
   );
CREATE TABLE Job (
   JobId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
   Job TINYBLOB NOT NULL,
   Name TINYBLOB NOT NULL,
   Type BINARY(1) NOT NULL,
   Level BINARY(1) NOT NULL,
   ClientId INTEGER NOT NULL REFERENCES Client,
   JobStatus BINARY(1) NOT NULL,
   SchedTime DATETIME NOT NULL,
   StartTime DATETIME NOT NULL,
   EndTime DATETIME NOT NULL,
   JobTDate BIGINT UNSIGNED NOT NULL,
   VolSessionId INTEGER UNSIGNED NOT NULL DEFAULT 0,
   VolSessionTime INTEGER UNSIGNED NOT NULL DEFAULT 0,
   JobFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
   JobBytes BIGINT UNSIGNED NOT NULL,
   JobErrors INTEGER UNSIGNED NOT NULL DEFAULT 0,
   JobMissingFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
   PoolId INTEGER UNSIGNED NOT NULL REFERENCES Pool,
   FileSetId INTEGER UNSIGNED NOT NULL REFERENCES FileSet,
   PurgedFiles TINYINT NOT NULL DEFAULT 0,
   HasBase TINYINT NOT NULL DEFAULT 0,
   PRIMARY KEY(JobId),
   INDEX (Name(128))
   );
CREATE TABLE FileSet (
   FileSetId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
   FileSet TINYBLOB NOT NULL,
   MD5 TINYBLOB NOT NULL,
   CreateTime DATETIME NOT NULL,
   PRIMARY KEY(FileSetId)
   );
CREATE TABLE JobMedia (
   JobMediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
   JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
   MediaId INTEGER UNSIGNED NOT NULL REFERENCES Media,
   FirstIndex INTEGER UNSIGNED NOT NULL DEFAULT 0,
   LastIndex INTEGER UNSIGNED NOT NULL DEFAULT 0,
   StartFile INTEGER UNSIGNED NOT NULL DEFAULT 0,
   EndFile INTEGER UNSIGNED NOT NULL DEFAULT 0,
   StartBlock INTEGER UNSIGNED NOT NULL DEFAULT 0,
   EndBlock INTEGER UNSIGNED NOT NULL DEFAULT 0,
   VolIndex INTEGER UNSIGNED NOT NULL DEFAULT 0,
   PRIMARY KEY(JobMediaId),
   INDEX (JobId, MediaId)
   );
CREATE TABLE Media (
   MediaId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
   VolumeName TINYBLOB NOT NULL,
   Slot INTEGER NOT NULL DEFAULT 0,
   PoolId INTEGER UNSIGNED NOT NULL REFERENCES Pool,
   MediaType TINYBLOB NOT NULL,
   FirstWritten DATETIME NOT NULL,
   LastWritten DATETIME NOT NULL,
   LabelDate DATETIME NOT NULL,
   VolJobs INTEGER UNSIGNED NOT NULL DEFAULT 0,
   VolFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
   VolBlocks INTEGER UNSIGNED NOT NULL DEFAULT 0,
   VolMounts INTEGER UNSIGNED NOT NULL DEFAULT 0,
   VolBytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
   VolErrors INTEGER UNSIGNED NOT NULL DEFAULT 0,
   VolWrites INTEGER UNSIGNED NOT NULL DEFAULT 0,
   VolCapacityBytes BIGINT UNSIGNED NOT NULL,
   VolStatus ENUM('Full', 'Archive', 'Append', 'Recycle', 'Purged',
    'Read-Only', 'Disabled', 'Error', 'Busy', 'Used', 'Cleaning') NOT NULL,
   Recycle TINYINT NOT NULL DEFAULT 0,
   VolRetention BIGINT UNSIGNED NOT NULL DEFAULT 0,
   VolUseDuration BIGINT UNSIGNED NOT NULL DEFAULT 0,
   MaxVolJobs INTEGER UNSIGNED NOT NULL DEFAULT 0,
   MaxVolFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
   MaxVolBytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
   InChanger TINYINT NOT NULL DEFAULT 0,
   MediaAddressing TINYINT NOT NULL DEFAULT 0,
   VolReadTime BIGINT UNSIGNED NOT NULL DEFAULT 0,
   VolWriteTime BIGINT UNSIGNED NOT NULL DEFAULT 0,
   PRIMARY KEY(MediaId),
   INDEX (PoolId)
   );
CREATE TABLE Pool (
   PoolId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
   Name TINYBLOB NOT NULL,
   NumVols INTEGER UNSIGNED NOT NULL DEFAULT 0,
   MaxVols INTEGER UNSIGNED NOT NULL DEFAULT 0,
   UseOnce TINYINT NOT NULL,
   UseCatalog TINYINT NOT NULL,
   AcceptAnyVolume TINYINT DEFAULT 0,
   VolRetention BIGINT UNSIGNED NOT NULL,
   VolUseDuration BIGINT UNSIGNED NOT NULL,
   MaxVolJobs INTEGER UNSIGNED NOT NULL DEFAULT 0,
   MaxVolFiles INTEGER UNSIGNED NOT NULL DEFAULT 0,
   MaxVolBytes BIGINT UNSIGNED NOT NULL,
   AutoPrune TINYINT DEFAULT 0,
   Recycle TINYINT DEFAULT 0,
   PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration', 'Scratch') NOT NULL,
   LabelFormat TINYBLOB,
   Enabled TINYINT DEFAULT 1,
   ScratchPoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
   RecyclePoolId INTEGER UNSIGNED DEFAULT 0 REFERENCES Pool,
   UNIQUE (Name(128)),
   PRIMARY KEY (PoolId)
   );
CREATE TABLE Client (
   ClientId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
   Name TINYBLOB NOT NULL,
   Uname TINYBLOB NOT NULL,       /* full uname -a of client */
   AutoPrune TINYINT DEFAULT 0,
   FileRetention BIGINT UNSIGNED NOT NULL,
   JobRetention  BIGINT UNSIGNED NOT NULL,
   UNIQUE (Name(128)),
   PRIMARY KEY(ClientId)
   );
CREATE TABLE BaseFiles (
   BaseId INTEGER UNSIGNED AUTO_INCREMENT,
   BaseJobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
   JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
   FileId INTEGER UNSIGNED NOT NULL REFERENCES File,
   FileIndex INTEGER UNSIGNED,
   PRIMARY KEY(BaseId)
   );
CREATE TABLE UnsavedFiles (
   UnsavedId INTEGER UNSIGNED AUTO_INCREMENT,
   JobId INTEGER UNSIGNED NOT NULL REFERENCES Job,
   PathId INTEGER UNSIGNED NOT NULL REFERENCES Path,
   FilenameId INTEGER UNSIGNED NOT NULL REFERENCES Filename,
   PRIMARY KEY (UnsavedId)
   );
CREATE TABLE Version (
   VersionId INTEGER UNSIGNED NOT NULL
   );
-- Initialize Version
INSERT INTO Version (VersionId) VALUES (7);
CREATE TABLE Counters (
   Counter TINYBLOB NOT NULL,
   MinValue INTEGER,
   MaxValue INTEGER,
   CurrentValue INTEGER,
   WrapCounter TINYBLOB NOT NULL,
   PRIMARY KEY (Counter(128))
   );
\end{lstlisting}
\normalsize