Extend local database once more.

This should hopefully add most of the features needed for the moment.
Improvements made:
  - Normalize segment names by putting them in a separate table and
    referring to them by ID everywhere else.  This should help quite a bit,
    since segment names are ~36 characters long.  Add conversion functions
    to the C++ code, which aren't optimized yet (we should cache results in
    the C++ code and not re-query the database each time).
  - Similarly, normalize snapshot names.
  - Add an expired field to the object index, so that we can stop using
    objects in future backups but still remember how old the data is.
Some more work is still needed in the C++ code, but the hope is that the
database schema itself will be more stable now.

diff --git a/localdb.cc b/localdb.cc
index 9bd33d9..cc88401 100644 (file)
--- a/localdb.cc
+++ b/localdb.cc
@@ -26,8 +26,6 @@ void LocalDb::Open(const char *path, const char *snapshot_name)
 {
     int rc;
 
-    snapshot = snapshot_name;
-
     rc = sqlite3_open(path, &db);
     if (rc) {
         fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
@@ -41,6 +39,36 @@ void LocalDb::Open(const char *path, const char *snapshot_name)
         sqlite3_close(db);
         throw IOException("Error starting transaction");
     }
+
+    /* Insert this snapshot into the database, and determine the integer key
+     * which will be used to identify it. */
+    sqlite3_stmt *stmt;
+    static const char s[] =
+        "insert into snapshots(name, timestamp) "
+        "values (?, julianday('now'))";
+    const char *tail;
+
+    rc = sqlite3_prepare_v2(db, s, strlen(s), &stmt, &tail);
+    if (rc != SQLITE_OK) {
+        sqlite3_close(db);
+        throw IOException("Error adding snapshot");
+    }
+
+    sqlite3_bind_text(stmt, 1, snapshot_name, strlen(snapshot_name),
+                      SQLITE_TRANSIENT);
+
+    rc = sqlite3_step(stmt);
+    if (rc != SQLITE_DONE) {
+        sqlite3_close(db);
+        throw IOException("Database execution error!");
+    }
+
+    snapshotid = sqlite3_last_insert_rowid(db);
+    sqlite3_finalize(stmt);
+    if (snapshotid == 0) {
+        sqlite3_close(db);
+        throw IOException("Find snapshot id");
+    }
 }
 
 void LocalDb::Close()
@@ -53,14 +81,90 @@ void LocalDb::Close()
     sqlite3_close(db);
 }
 
+int64_t LocalDb::SegmentToId(const string &segment)
+{
+    int rc;
+    sqlite3_stmt *stmt;
+    static const char s1[] =
+        "insert or ignore into segments(segment) values (?);";
+    static const char s2[] =
+        "select segmentid from segments where segment = ?";
+    const char *tail;
+    int64_t result;
+
+    rc = sqlite3_prepare_v2(db, s1, strlen(s1), &stmt, &tail);
+    if (rc != SQLITE_OK) {
+        throw IOException("Find id by segment name");
+    }
+    sqlite3_bind_text(stmt, 1, segment.c_str(), segment.size(),
+                      SQLITE_TRANSIENT);
+    rc = sqlite3_step(stmt);
+    if (rc != SQLITE_DONE) {
+        throw IOException("Could not execute INSERT statement!");
+    }
+    sqlite3_finalize(stmt);
+
+    rc = sqlite3_prepare_v2(db, s2, strlen(s2), &stmt, &tail);
+    if (rc != SQLITE_OK) {
+        throw IOException("Find id by segment name");
+    }
+
+    sqlite3_bind_text(stmt, 1, segment.c_str(), segment.size(),
+                      SQLITE_TRANSIENT);
+
+    rc = sqlite3_step(stmt);
+    if (rc == SQLITE_DONE) {
+        throw IOException("No segment found by id");
+    } else if (rc == SQLITE_ROW) {
+        result = sqlite3_column_int64(stmt, 0);
+    } else {
+        throw IOException("Error executing find segment by id query");
+    }
+
+    sqlite3_finalize(stmt);
+
+    return result;
+}
+
+string LocalDb::IdToSegment(int64_t segmentid)
+{
+    int rc;
+    sqlite3_stmt *stmt;
+    static const char s[] =
+        "select segment from segments where segmentid = ?";
+    const char *tail;
+    string result;
+
+    rc = sqlite3_prepare_v2(db, s, strlen(s), &stmt, &tail);
+    if (rc != SQLITE_OK) {
+        throw IOException("Find segment by id");
+    }
+
+    sqlite3_bind_int64(stmt, 1, segmentid);
+
+    rc = sqlite3_step(stmt);
+    if (rc == SQLITE_DONE) {
+        throw IOException("No segment found by id");
+    } else if (rc == SQLITE_ROW) {
+        result = (const char *)sqlite3_column_text(stmt, 0);
+    } else {
+        throw IOException("Error executing find segment by id query");
+    }
+
+    sqlite3_finalize(stmt);
+
+    return result;
+}
+
 void LocalDb::StoreObject(const ObjectReference& ref,
                           const string &checksum, int64_t size)
 {
     int rc;
     sqlite3_stmt *stmt;
     static const char s[] =
-        "insert into block_index(segment, object, checksum, size, timestamp) "
-        "values (?, ?, ?, ?, julianday('now'))";
+        "insert into "
+        "block_index(segmentid, object, checksum, size, timestamp, expired) "
+        "values (?, ?, ?, ?, julianday('now'), 0)";
     const char *tail;
 
     rc = sqlite3_prepare_v2(db, s, strlen(s), &stmt, &tail);
@@ -68,8 +172,7 @@ void LocalDb::StoreObject(const ObjectReference& ref,
         return;
     }
 
-    string seg = ref.get_segment();
-    sqlite3_bind_text(stmt, 1, seg.c_str(), seg.size(), SQLITE_TRANSIENT);
+    sqlite3_bind_int64(stmt, 1, SegmentToId(ref.get_segment()));
     string obj = ref.get_sequence();
     sqlite3_bind_text(stmt, 2, obj.c_str(), obj.size(), SQLITE_TRANSIENT);
     sqlite3_bind_text(stmt, 3, checksum.c_str(), checksum.size(),
@@ -89,8 +192,8 @@ ObjectReference LocalDb::FindObject(const string &checksum, int64_t size)
     int rc;
     sqlite3_stmt *stmt;
     static const char s[] =
-        "select segment, object from block_index "
-        "where checksum = ? and size = ?";
+        "select segmentid, object from block_index "
+        "where checksum = ? and size = ? and expired = 0";
     const char *tail;
 
     ObjectReference ref;
@@ -107,7 +210,7 @@ ObjectReference LocalDb::FindObject(const string &checksum, int64_t size)
     rc = sqlite3_step(stmt);
     if (rc == SQLITE_DONE) {
     } else if (rc == SQLITE_ROW) {
-        ref = ObjectReference((const char *)sqlite3_column_text(stmt, 0),
+        ref = ObjectReference(IdToSegment(sqlite3_column_int64(stmt, 0)),
                               (const char *)sqlite3_column_text(stmt, 1));
     } else {
         fprintf(stderr, "Could not execute SELECT statement!\n");
@@ -123,9 +226,9 @@ void LocalDb::UseObject(const ObjectReference& ref)
     int rc;
     sqlite3_stmt *stmt;
     static const char s[] =
-        "insert into snapshot_contents "
-        "select blockid, ? as snapshot from block_index "
-        "where segment = ? and object = ?";
+        "insert or ignore into snapshot_contents "
+        "select blockid, ? as snapshotid from block_index "
+        "where segmentid = ? and object = ?";
     const char *tail;
 
     rc = sqlite3_prepare_v2(db, s, strlen(s), &stmt, &tail);
@@ -133,10 +236,8 @@ void LocalDb::UseObject(const ObjectReference& ref)
         return;
     }
 
-    sqlite3_bind_text(stmt, 1, snapshot.c_str(), snapshot.size(),
-                      SQLITE_TRANSIENT);
-    string seg = ref.get_segment();
-    sqlite3_bind_text(stmt, 2, seg.c_str(), seg.size(), SQLITE_TRANSIENT);
+    sqlite3_bind_int64(stmt, 1, snapshotid);
+    sqlite3_bind_int64(stmt, 2, SegmentToId(ref.get_segment()));
     string obj = ref.get_sequence();
     sqlite3_bind_text(stmt, 3, obj.c_str(), obj.size(), SQLITE_TRANSIENT);
 

diff --git a/localdb.h b/localdb.h
index 3a88870..eb0955d 100644 (file)
--- a/localdb.h
+++ b/localdb.h
@@ -28,8 +28,11 @@ public:
     ObjectReference FindObject(const std::string &checksum, int64_t size);
     void UseObject(const ObjectReference& ref);
 private:
-    std::string snapshot;
     sqlite3 *db;
+    int64_t snapshotid;
+
+    int64_t SegmentToId(const std::string &segment);
+    std::string IdToSegment(int64_t segmentid);
 };
 
 #endif // _LBS_LOCALDB_H

diff --git a/schema.sql b/schema.sql
index 096069b..d225ec1 100644 (file)
--- a/schema.sql
+++ b/schema.sql
@@ -3,20 +3,36 @@
 --
 -- The index is stored in an SQLite3 database.  This is its schema.
 
+-- List of snapshots which have been created.
+create table snapshots (
+    snapshotid integer primary key,
+    name text not null,
+    timestamp real
+);
+
+-- List of segments which have been created.
+create table segments (
+    segmentid integer primary key,
+    segment text unique not null
+);
+
 -- Index of all blocks which have been stored in a snapshot, by checksum.
 create table block_index (
     blockid integer primary key,
-    segment text,
-    object text,
+    segmentid integer not null,
+    object text not null,
     checksum text,
     size integer,
-    timestamp real
+    timestamp real,
+    expired integer
 );
 create index block_content_index on block_index(checksum);
-create index block_name_index on block_index(segment, object);
+create unique index block_name_index on block_index(segmentid, object);
 
 -- Index tracking which blocks are used by which snapshots.
 create table snapshot_contents (
     blockid integer,
-    snapshot text
+    snapshotid integer
 );
+create unique index snapshot_contents_unique
+    on snapshot_contents(blockid, snapshotid);