Changeset 27839 for branches/simmosaic_branches/Nebulous-Server/lib/Nebulous/Server/SQL.pm

Timestamp:

May 3, 2010, 8:45:22 AM (16 years ago)

Author:

eugene

Message:

updates from trunk

Location:

branches/simmosaic_branches

Files:

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

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

@@ -183 +183 @@
         WHERE ins_id = ?
     },
-    delete_instance_by_so_id => qq{
-        DELETE FROM instance
-        WHERE so_id = ?
-    },
     get_object_from_uri   => qq{
         SELECT so_id
@@ -218 +214 @@
         GROUP BY so_id
     },
-    copy_instances_to_deleted => qq{
+    copy_dead_instances_to_deleted => qq{
         INSERT INTO deleted 
         SELECT vol_id, uri, NULL
         FROM instance
+        LEFT JOIN mountedvol
+            USING(vol_id)
         WHERE
             so_id = ?
+            AND (mountedvol.available IS NULL || mountedvol.available = 0)
+    },
+    find_dead_instances_by_so_id => qq{
+        SELECT so_id, ins_id, vol_id, uri
+        FROM instance
+        LEFT JOIN mountedvol
+            USING(vol_id)
+        WHERE
+            so_id = ?
+            AND (mountedvol.available IS NULL || mountedvol.available = 0)
     },
     get_object_instances    => qq{
@@ -229 +237 @@
             storage_object.so_id,
             uri,
-            available
+            mountedvol.available,
+            vol_id,
+            cab_id
         FROM storage_object
         JOIN instance
@@ -235 +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
@@ -247 +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
@@ -267 +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,
@@ -303 +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{
@@ -323 +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
@@ -329 +377 @@
     },
     find_object_by_ext_id => qq{
-        SELECT ext_id, ext_id_basename
+        SELECT so_id, ext_id, ext_id_basename
         FROM storage_object
         WHERE ext_id = ?
@@ -606 +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,
@@ -615 +674 @@
     xattr BOOLEAN DEFAULT FALSE,
     mountpoint VARCHAR(255) NOT NULL,
+    cab_id INT,
     PRIMARY KEY(vol_id),
     UNIQUE KEY(name),
@@ -621 +681 @@
     KEY(allocate),
     KEY(available),
+    FOREIGN KEY(cab_id) REFERENCES cabinet(cab_id),
     KEY(mountpoint(255))
 ) ENGINE=innodb DEFAULT CHARSET=latin1;