lib/dbox/database.rb in dbox-0.6.15 vs lib/dbox/database.rb in dbox-0.7.0

@@ -53,24 +53,25 @@
 
     def ensure_schema_exists
       @db.execute_batch(%{
         CREATE TABLE IF NOT EXISTS metadata (
           id           integer PRIMARY KEY AUTOINCREMENT NOT NULL,
-          remote_path  varchar(255) NOT NULL,
+          remote_path  text COLLATE NOCASE UNIQUE NOT NULL,
           version      integer NOT NULL
         );
         CREATE TABLE IF NOT EXISTS entries (
           id           integer PRIMARY KEY AUTOINCREMENT NOT NULL,
-          path         varchar(255) UNIQUE NOT NULL,
+          path         text COLLATE NOCASE UNIQUE NOT NULL,
           is_dir       boolean NOT NULL,
           parent_id    integer REFERENCES entries(id) ON DELETE CASCADE,
-          local_hash   varchar(255),
-          remote_hash  varchar(255),
+          local_hash   text,
+          remote_hash  text,
           modified     datetime,
-          revision     varchar(255)
+          revision     text
         );
         CREATE INDEX IF NOT EXISTS entry_parent_ids ON entries(parent_id);
+        CREATE INDEX IF NOT EXISTS entry_path ON entries(path);
       })
     end
 
     def migrate
       # removing local_path from metadata
@@ -80,11 +81,11 @@
         @db.execute_batch(%{
           BEGIN TRANSACTION;
           ALTER TABLE metadata RENAME TO metadata_old;
           CREATE TABLE metadata (
             id           integer PRIMARY KEY AUTOINCREMENT NOT NULL,
-            remote_path  varchar(255) NOT NULL,
+            remote_path  text NOT NULL,
             version      integer NOT NULL
           );
           INSERT INTO metadata SELECT id, remote_path, version FROM metadata_old;
           DROP TABLE metadata_old;
           UPDATE metadata SET version = 2;
@@ -119,16 +120,16 @@
         @db.execute_batch(%{
           BEGIN TRANSACTION;
           ALTER TABLE entries RENAME TO entries_old;
           CREATE TABLE entries (
             id           integer PRIMARY KEY AUTOINCREMENT NOT NULL,
-            path         varchar(255) UNIQUE NOT NULL,
+            path         text UNIQUE NOT NULL,
             is_dir       boolean NOT NULL,
             parent_id    integer REFERENCES entries(id) ON DELETE CASCADE,
-            hash         varchar(255),
+            hash         text,
             modified     datetime,
-            revision     varchar(255)
+            revision     text
           );
           INSERT INTO entries SELECT id, path, is_dir, parent_id, hash, modified, null FROM entries_old;
         })
 
         # copy in the new revision IDs
@@ -151,17 +152,17 @@
         @db.execute_batch(%{
           BEGIN TRANSACTION;
           ALTER TABLE entries RENAME TO entries_old;
           CREATE TABLE entries (
             id           integer PRIMARY KEY AUTOINCREMENT NOT NULL,
-            path         varchar(255) UNIQUE NOT NULL,
+            path         text UNIQUE NOT NULL,
             is_dir       boolean NOT NULL,
             parent_id    integer REFERENCES entries(id) ON DELETE CASCADE,
-            local_hash   varchar(255),
-            remote_hash  varchar(255),
+            local_hash   text,
+            remote_hash  text,
             modified     datetime,
-            revision     varchar(255)
+            revision     text
           );
           INSERT INTO entries SELECT id, path, is_dir, parent_id, null, hash, modified, revision FROM entries_old;
         })
 
         # calculate hashes on files with same timestamp as we have (as that was the previous mechanism used to check freshness)
@@ -179,19 +180,63 @@
           DROP TABLE entries_old;
           UPDATE metadata SET version = 4;
           COMMIT;
         })
       end
+
+      if metadata[:version] < 5
+        log.info "Migrating to database schema v5"
+
+        # make path be case insensitive
+        @db.execute_batch(%{
+          BEGIN TRANSACTION;
+
+          -- migrate metadata table
+          ALTER TABLE metadata RENAME TO metadata_old;
+          CREATE TABLE IF NOT EXISTS metadata (
+            id           integer PRIMARY KEY AUTOINCREMENT NOT NULL,
+            remote_path  text COLLATE NOCASE UNIQUE NOT NULL,
+            version      integer NOT NULL
+          );
+          INSERT INTO metadata SELECT id, remote_path, version FROM metadata_old;
+          DROP TABLE metadata_old;
+
+          -- migrate entries table
+          ALTER TABLE entries RENAME TO entries_old;
+          CREATE TABLE entries (
+            id           integer PRIMARY KEY AUTOINCREMENT NOT NULL,
+            path         text COLLATE NOCASE UNIQUE NOT NULL,
+            is_dir       boolean NOT NULL,
+            parent_id    integer REFERENCES entries(id) ON DELETE CASCADE,
+            local_hash   text,
+            remote_hash  text,
+            modified     datetime,
+            revision     text
+          );
+          INSERT INTO entries SELECT id, path, is_dir, parent_id, local_hash, remote_hash, modified, revision FROM entries_old;
+          DROP TABLE entries_old;
+
+          -- recreate indexes
+          DROP INDEX IF EXISTS entry_parent_ids;
+          DROP INDEX IF EXISTS entry_path;
+          CREATE INDEX entry_parent_ids ON entries(parent_id);
+          CREATE INDEX entry_path ON entries(path);
+
+          -- update version
+          UPDATE metadata SET version = 5;
+          COMMIT;
+        })
+      end
     end
 
     METADATA_COLS = [ :remote_path, :version ] # don't need to return id
     ENTRY_COLS    = [ :id, :path, :is_dir, :parent_id, :local_hash, :remote_hash, :modified, :revision ]
 
     def bootstrap(remote_path)
       @db.execute(%{
         INSERT INTO metadata (remote_path, version) VALUES (?, ?);
-      }, remote_path, 4)
+      }, remote_path, 5)
       @db.execute(%{
         INSERT INTO entries (path, is_dir) VALUES (?, ?)
       }, "", 1)
     end
 
@@ -339,9 +384,11 @@
     def entry_res_to_fields(entry_cols, res)
       if res
         h = make_fields(entry_cols, res)
         h[:is_dir] = (h[:is_dir] == 1)
         h[:modified]  = Time.at(h[:modified]) if h[:modified]
+        h[:local_path] = relative_to_local_path(h[:path])
+        h[:remote_path] = relative_to_remote_path(h[:path])
         h
       else
         nil
       end
     end