"Fossies" - the Fresh Open Source Software Archive

Member "Tardis-1.2.1/src/Tardis/schema/tardis.sql" (9 Jun 2021, 4428 Bytes) of package /linux/privat/Tardis-1.2.1.tar.gz:


As a special service "Fossies" has tried to format the requested source page into HTML format using (guessed) PL/SQL source code syntax highlighting (style: standard) with prefixed line numbers. Alternatively you can here view or download the uninterpreted source code file. See also the latest Fossies "Diffs" side-by-side code changes report for "tardis.sql": 1.1.5_vs_1.2.1.

    1 PRAGMA journal_mode=truncate;
    2 
    3 CREATE TABLE IF NOT EXISTS Config (
    4     Key             TEXT PRIMARY KEY,
    5     Value           TEXT NOT NULL,
    6     Timestamp       DATETIME DEFAULT CURRENT_TIMESTAMP
    7 
    8 );
    9 
   10 CREATE TABLE IF NOT EXISTS ClientConfig (
   11     ClientConfigId  INTEGER PRIMARY KEY AUTOINCREMENT,
   12     ClientConfig    TEXT
   13 );
   14 
   15 
   16 CREATE TABLE IF NOT EXISTS Backups (
   17     Name            TEXT UNIQUE,
   18     BackupSet       INTEGER PRIMARY KEY AUTOINCREMENT,
   19     StartTime       TEXT,
   20     EndTime         TEXT,
   21     PurgeTime       TEXT,
   22     ClientEndTime   TEXT,
   23     ClientTime      TEXT,
   24     Session         TEXT UNIQUE,
   25     Completed       INTEGER DEFAULT 0,
   26     Priority        INTEGER DEFAULT 1,
   27     Full            INTEGER DEFAULT 0,
   28     Vacuumed        INTEGER DEFAULT 0,
   29     ClientVersion   TEXT,
   30     ServerVersion   TEXT,
   31     SchemaVersion   INTEGER,
   32     ClientIP        TEXT,
   33     FilesFull       INTEGER,
   34     FilesDelta      INTEGER,
   35     BytesReceived   INTEGER,
   36     ClientConfigId  INTEGER,
   37     CmdLineId       INTEGER,
   38     ServerSession   TEXT,
   39     Exception       TEXT,
   40     ErrorMsg        TEXT,
   41     FOREIGN KEY(ClientConfigId) REFERENCES ClientConfig(ClientConfigId),
   42     FOREIGN KEY(CmdLineId) REFERENCES Checksums(ChecksumId)
   43 );
   44 
   45 CREATE TABLE IF NOT EXISTS CheckSums (
   46     Checksum    TEXT UNIQUE NOT NULL,
   47     ChecksumId  INTEGER PRIMARY KEY AUTOINCREMENT,
   48     Size        INTEGER,
   49     Basis       INTEGER,
   50     DeltaSize   INTEGER,
   51     DiskSize    INTEGER,
   52     Compressed  TEXT,
   53     Encrypted   INTEGER,            -- Boolean
   54     ChainLength INTEGER,
   55     Added       INTEGER,            -- References BackupSet, but not foreign key, as sets can be deleted.
   56     IsFile      INTEGER,            -- Boolean, is there a file backing this checksum
   57     FOREIGN KEY(Basis) REFERENCES CheckSums(Checksum)
   58 );
   59 
   60 CREATE TABLE IF NOT EXISTS Names (
   61     Name        TEXT UNIQUE NOT NULL,
   62     NameId      INTEGER PRIMARY KEY AUTOINCREMENT
   63 );
   64 
   65 CREATE TABLE IF NOT EXISTS Files (
   66     NameId      INTEGER  NOT NULL,
   67     FirstSet    INTEGER  NOT NULL,
   68     LastSet     INTEGER  NOT NULL,
   69     Inode       INTEGER  NOT NULL,
   70     Device      INTEGER  NOT NULL,
   71     Parent      INTEGER  NOT NULL,
   72     ParentDev   INTEGER  NOT NULL,
   73     ChecksumId  INTEGER,            -- On a file, represents the file data.  On a directory, the hash of the filenames in the directory.
   74                                     -- On a directory, this can be rewritten over time, and is the most recent hash.
   75     Dir         INTEGER,
   76     Link        INTEGER,
   77     MTime       INTEGER,
   78     CTime       INTEGER,
   79     ATime       INTEGER,
   80     Mode        INTEGER,
   81     UID         INTEGER,
   82     GID         INTEGER, 
   83     NLinks      INTEGER,
   84     XattrID     INTEGER,
   85     AclID       INTEGER,
   86 
   87     PRIMARY KEY(NameId, FirstSet, LastSet, Parent, ParentDev),
   88     FOREIGN KEY(NameId)      REFERENCES Names(NameId),
   89     FOREIGN KEY(ChecksumId)  REFERENCES CheckSums(ChecksumId)
   90     FOREIGN KEY(XattrID)     REFERENCES CheckSums(ChecksumId)
   91     FOREIGN KEY(AclID)       REFERENCES CheckSums(ChecksumId)
   92 );
   93 
   94 CREATE INDEX IF NOT EXISTS CheckSumIndex ON CheckSums(Checksum);
   95 
   96 CREATE INDEX IF NOT EXISTS InodeFirstIndex ON Files(Inode ASC, Device ASC, FirstSet ASC);
   97 CREATE INDEX IF NOT EXISTS ParentFirstIndex ON Files(Parent ASC, ParentDev ASC, FirstSet ASC);
   98 CREATE INDEX IF NOT EXISTS InodeLastIndex ON Files(Inode ASC, Device ASC, LastSet ASC);
   99 CREATE INDEX IF NOT EXISTS ParentLastndex ON Files(Parent ASC, ParentDev ASC, LastSet ASC);
  100 CREATE INDEX IF NOT EXISTS NameIndex ON Names(Name ASC);
  101 CREATE INDEX IF NOT EXISTS InodeIndex ON Files(Inode ASC, Device ASC, Parent ASC, ParentDev ASC, FirstSet ASC, LastSet ASC);
  102 
  103 INSERT OR IGNORE INTO Backups (Name, StartTime, EndTime, ClientTime, Completed, Priority, FilesFull, FilesDelta, BytesReceived) VALUES (".Initial", 0, 0, 0, 1, 0, 0, 0, 0);
  104     
  105 CREATE VIEW IF NOT EXISTS VFiles AS
  106     SELECT Names.Name AS Name, Inode, Device, Parent, ParentDev, Dir, Link, Size, MTime, CTime, ATime, Mode, UID, GID, NLinks, Checksum, Backups.BackupSet, Backups.Name AS Backup
  107     FROM Files
  108     JOIN Names ON Files.NameId = Names.NameId
  109     JOIN Backups ON Backups.BackupSet BETWEEN Files.FirstSet AND Files.LastSet
  110     LEFT OUTER JOIN CheckSums ON Files.ChecksumId = CheckSums.ChecksumId;
  111 
  112 INSERT OR REPLACE INTO Config (Key, Value) VALUES ("SchemaVersion", "18");
  113 INSERT OR REPLACE INTO Config (Key, Value) VALUES ("VacuumInterval", "5");