Changeset 39662 for branches/czw_branch/20160809/Nebulous-Server/lib/Nebulous/Server/SQL.pm

Timestamp:

Aug 30, 2016, 1:38:49 PM (10 years ago)

Author:

watersc1

Message:

Commit of test version of Nebulous location based code.

File:

• branches/czw_branch/20160809/Nebulous-Server/lib/Nebulous/Server/SQL.pm (modified) (8 diffs)

branches/czw_branch/20160809/Nebulous-Server/lib/Nebulous/Server/SQL.pm

@@ -250 +250 @@
             AND mountedvol.available = ?
     },
+    get_object_instances_by_proximity  => qq{
+        SELECT
+            storage_object.so_id,
+            uri,
+            mountedvol.available,
+            vol_id,
+            cab_id,
+            (ABS(vol_id - ?) + 20 * ABS(cab_id - ?) + 100 * ABS(site_id - ?)) AS vol_idx
+        FROM storage_object
+        JOIN instance
+            USING (so_id)
+        JOIN mountedvol
+            USING(vol_id)
+        JOIN volume
+            USING(vol_id)
+        JOIN cabinet
+            USING(cab_id)
+        WHERE ext_id = ?
+          AND mountedvol.available = ?
+        ORDER BY vol_idx ASC
+    },
     get_object_instances_by_vol_name => qq{
         SELECT
@@ -301 +322 @@
     get_cabinets_for_ext_id            => qq{
         SELECT DISTINCT 
-            cab_id 
+            cab_id, site_id
         FROM instance 
-        JOIN volume ON instance.vol_id = volume.vol_id 
+        JOIN volume ON (instance.vol_id = volume.vol_id)
+        JOIN cabinet ON (volume.cab_id  = cabinet.cab_id)
         JOIN storage_object USING(so_id) 
         WHERE ext_id = ?
@@ -317 +339 @@
         FROM mountedvol AS m
         JOIN volume AS v USING(vol_id)
+        JOIN cabinet AS c USING(cab_id)
         LEFT JOIN (
                    SELECT
@@ -336 +359 @@
              AND m.available = ?
              AND m.allocate = ?
-             AND m.xattr = 0
+--             AND m.xattr = 0
              AND ( (v.cab_id IS NULL) ||
                    (v.cab_id != ?) )
+             AND ( (c.site_id IS NULL) ||
+                   (c.site_id != ?) )
          ORDER BY free DESC
          LIMIT ?) as topfew
@@ -366 +391 @@
     },
     new_cabinet         => qq{
-        INSERT INTO cabinet (name, location, cab_id)
-        VALUES (?, ?, NULL)
+        INSERT INTO cabinet (name, location, site_id, cab_id)
+        VALUES (?, ?, ?, NULL)
+    },
+    update_cabinet      => qq{
+        UPDATE cabinet SET 
+           location = ?,
+           name     = ?,
+           site_id  = ?
+        WHERE cab_id = ?
     },
     new_volume          => qq{
@@ -373 +405 @@
         VALUES (?, ?, ?, TRUE, TRUE, FALSE, ?, NULL, ?)
     },
+    new_alias          => qq{
+        INSERT INTO aliasvol (alias_id, alias, name, vol_id)
+        VALUES (NULL, ?, ?, ?)
+    },
+    update_alias       => qq{
+        UPDATE alias SET
+          vol_id = ?,
+          name   = ?
+        WHERE alias_id = ?
+        AND   alias    = ?
+    },
     get_volume_by_name => qq{
         SELECT vol_id, name, host, path
+        FROM volume
+        WHERE name = ?
+    },
+    get_volume_by_alias => qq{
+        SELECT vol_id, name, host, path
+        FROM aliasvol
+        JOIN volume USING(vol_id,name)
+        WHERE alias = ?
+    },
+    get_site_info_by_name => qq{
+        SELECT vol_id, cab_id, site_id
         FROM volume
         WHERE name = ?
@@ -676 +730 @@
     cab_id INT NOT NULL AUTO_INCREMENT,
     name VARCHAR(255) NOT NULL,
+    site_id INT NOT NULL DEFAULT 0,
     location VARCHAR(255),
     PRIMARY KEY(cab_id),
     UNIQUE KEY(name),
+    KEY (site_id),
     KEY (location)
 ) ENGINE=innodb DEFAULT CHARSET=latin1;
@@ -735 +791 @@
 ###
 
+CREATE TABLE aliasvol (
+    alias_id INT NOT NULL AUTO_INCREMENT,
+    alias VARCHAR(255) NOT NULL,
+    name  VARCHAR(255) NOT NULL,
+    vol_id INT NOT NULL,
+    PRIMARY KEY(alias_id),
+    KEY(alias),
+    KEY(name),
+    FOREIGN KEY(vol_id) REFERENCES volume(vol_id)
+) ENGINE=innodb DEFAULT CHARSET=latin1;
+
+###
+
 CREATE TABLE instance (
     ins_id BIGINT NOT NULL AUTO_INCREMENT,