Changeset 27838 for branches/tap_branches/Nebulous-Server/lib/Nebulous/Server/SQL.pm

Timestamp:

May 3, 2010, 8:41:49 AM (16 years ago)

Author:

eugene

Message:

updates from trunk

Location:

branches/tap_branches

Files:

• . (modified) (1 prop)
• Nebulous-Server/lib/Nebulous/Server/SQL.pm (modified) (9 diffs)

branches/tap_branches/Nebulous-Server/lib/Nebulous/Server/SQL.pm

@@ -237 +237 @@
             storage_object.so_id,
             uri,
-            available
+            mountedvol.available,
+            vol_id,
+            cab_id
         FROM storage_object
         JOIN instance
@@ -243 +245 @@
         JOIN mountedvol
             USING(vol_id)
-        WHERE ext_id = ?
-            AND available = ?
+        JOIN volume
+            USING(vol_id)
+        WHERE ext_id = ?
+            AND mountedvol.available = ?
     },
     get_object_instances_by_vol_name => qq{
         SELECT
             storage_object.so_id,
-            uri
+            uri,
+            vol_id,
+            cab_id
         FROM storage_object
         JOIN instance
@@ -255 +261 @@
         JOIN mountedvol
             USING(vol_id)
-        WHERE ext_id = ?
-            AND name = ?
-            AND available = ?
-    },
+        JOIN volume
+            USING(vol_id)
+        WHERE ext_id = ?
+            AND mountedvol.name = ?
+            AND mountedvol.available = ?
+    },
+    # volume handler
     get_storage_volume_by_name   => qq{
         SELECT
@@ -275 +284 @@
         LIMIT 1
     },
-    get_replication_volume_for_ext_id   => qq{
+    # volume handler
+    get_cabinets_for_ext_id            => qq{
+        SELECT DISTINCT 
+            cab_id 
+        FROM instance 
+        JOIN volume ON instance.vol_id = volume.vol_id 
+        JOIN storage_object USING(so_id) 
+        WHERE ext_id = ?
+    },
+    get_replication_volume_for_ext_id    => qq{
+        SELECT * FROM (
         SELECT
             m.vol_id,
-            host,
-            path,
-            xattr,
+            m.host,
+            m.path,
+            m.xattr,
             total - used as free
         FROM mountedvol AS m
-        LEFT JOIN instance AS i
+        JOIN volume AS v USING(vol_id)
+        LEFT JOIN (
+                   SELECT
+                       instance.vol_id,
+                       so_id
+                   FROM instance
+                   JOIN volume 
+                   ON instance.vol_id = volume.vol_id
+                   WHERE so_id = (
+                                  SELECT so_id
+                                  FROM storage_object
+                                  WHERE ext_id = ?
+                                 )
+                  ) AS i
             ON m.vol_id = i.vol_id
-            AND i.so_id = (
-                SELECT so_id
-                FROM storage_object
-                WHERE ext_id = ?
-            )
-        WHERE
-            i.vol_id IS NULL
-            AND used / total < ? 
-            AND available = ? 
-            AND allocate = ? 
-            ORDER BY RAND()
-            LIMIT 1
-    },
+         WHERE
+             i.vol_id IS NULL
+             AND used / total < ?
+             AND m.available = ?
+             AND m.allocate = ?
+             AND ( (v.cab_id IS NULL) ||
+                   (v.cab_id != ?) )
+         ORDER BY free DESC
+         LIMIT ?) as topfew
+         ORDER BY RAND()
+         LIMIT 1
+    },
+    # volume handler
     get_storage_volume          => qq{
+        SELECT * from (
         SELECT
             vol_id,
@@ -311 +344 @@
             AND allocate = ?
         ORDER BY free DESC
+        LIMIT ?) as topfew
+        ORDER BY RAND()
         LIMIT 1
     },
+    new_cabinet         => qq{
+        INSERT INTO cabinet (name, location, cab_id)
+        VALUES (?, ?, NULL)
+    },
     new_volume          => qq{
-        INSERT INTO volume (name, host, path, allocate, available, xattr, mountpoint)
-        VALUES (?, ?, ?, TRUE, TRUE, FALSE, ?)
+        INSERT INTO volume (name, host, path, allocate, available, xattr, mountpoint, cab_id)
+        VALUES (?, ?, ?, TRUE, TRUE, FALSE, ?, NULL)
     },
     get_volume_by_name => qq{
@@ -331 +370 @@
             v.available,
             v.xattr,
-            mountedvol.vol_id IS NOT NULL as mounted
+            mountedvol.vol_id IS NOT NULL as mounted,
+            v.cab_id
         FROM volume AS v
         LEFT JOIN mountedvol
@@ -614 +654 @@
 ###
 
+CREATE TABLE cabinet (
+    cab_id INT NOT NULL AUTO_INCREMENT,
+    name VARCHAR(255) NOT NULL,
+    location VARCHAR(255),
+    PRIMARY KEY(cab_id),
+    UNIQUE KEY(name),
+    KEY (location)
+) ENGINE=innodb DEFAULT CHARSET=latin1;
+
+###
+
 CREATE TABLE volume (
     vol_id INT NOT NULL AUTO_INCREMENT,
@@ -623 +674 @@
     xattr BOOLEAN DEFAULT FALSE,
     mountpoint VARCHAR(255) NOT NULL,
+    cab_id INT,
     PRIMARY KEY(vol_id),
     UNIQUE KEY(name),
@@ -629 +681 @@
     KEY(allocate),
     KEY(available),
+    FOREIGN KEY(cab_id) REFERENCES cabinet(cab_id),
     KEY(mountpoint(255))
 ) ENGINE=innodb DEFAULT CHARSET=latin1;