Changeset 24557 for branches/eam_branches/20090522/Nebulous-Server/lib/Nebulous/Server/SQL.pm

Timestamp:

Jun 25, 2009, 2:00:56 PM (17 years ago)

Author:

eugene

Message:

merging changes from head

Location:

branches/eam_branches/20090522

Files:

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

branches/eam_branches/20090522/Nebulous-Server/lib/Nebulous/Server/SQL.pm

@@ -175 +175 @@
         WHERE ext_id = ?
     },
-    delete_instance     => qq{
+    delete_instance_by_ins_id => qq{
         DELETE FROM instance
-        WHERE uri = ?
+        WHERE ins_id = ?
     },
     get_object_from_uri   => qq{
@@ -184 +184 @@
         WHERE uri = ?
     },
+    get_instance_by_uri   => qq{
+        SELECT ins_id
+        FROM instance
+        WHERE
+            so_id = ?
+            AND uri = ?
+    },
     get_instance_count   => qq{
         SELECT count(ins_id)
         FROM instance
         WHERE so_id = ?
+    },
+    get_instance_count_by_ext_id   => qq{
+        SELECT count(ins_id), so_id
+        FROM instance
+        JOIN storage_object
+            USING(so_id)
+        WHERE
+            ext_id = ?
+        GROUP BY so_id
     },
     get_object_instances    => qq{
@@ -233 +249 @@
     get_replication_volume_for_ext_id   => qq{
         SELECT
-            vol_id,
+            m.vol_id,
             host,
             path,
             xattr,
             total - used as free
-        FROM (
--- This query works but is slow...
---             SELECT
---                  mountedvol.*
---             FROM mountedvol
---             WHERE
---                 vol_id NOT IN(
---                     SELECT vol_id
---                     FROM storage_object
---                     JOIN instance
---                         USING(so_id)
---                     WHERE ext_id = --
---                  )
-                SELECT
-                    m.*
-                FROM mountedvol AS m
-                LEFT JOIN instance 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
-        ) as Foo 
+        FROM mountedvol AS m
+        LEFT JOIN instance AS i
+            ON m.vol_id = i.vol_id
+            AND i.so_id = (
+                SELECT so_id
+                FROM storage_object
+                WHERE ext_id = ?
+            )
         WHERE
-            used / total < ? 
+            i.vol_id IS NULL
+            AND used / total < ? 
             AND available = ? 
             AND allocate = ? 
-        ORDER BY RAND()
-        LIMIT 1
+            ORDER BY RAND()
+            LIMIT 1
     },
     get_storage_volume          => qq{
@@ -287 +286 @@
     },
     new_volume          => qq{
-        INSERT INTO volume (name, host, path, allocate, available, xattr)
-        VALUES (?, ?, ?, TRUE, TRUE, FALSE)
+        INSERT INTO volume (name, host, path, allocate, available, xattr, mountpoint)
+        VALUES (?, ?, ?, TRUE, TRUE, FALSE, ?)
     },
     get_volume_by_name => qq{
@@ -397 +396 @@
     },
     get_mounted_volumes => qq{
-        SELECT * FROM mountedvol ORDER BY host, name
+        SELECT mountpoint, total, used, vol_id, name, host, path, allocate, available, xattr FROM mountedvol ORDER BY host, name
     },
 );
@@ -424 +423 @@
 DROP TABLE IF EXISTS lock_record;
 DROP TABLE IF EXISTS volume;
-DROP TABLE IF EXISTS mount;
+DROP TABLE IF EXISTS mountedvol;
 DROP TABLE IF EXISTS log;
-DROP TABLE IF EXISTS mountedvol;
 DROP TABLE IF EXISTS directory;
 DROP PROCEDURE IF EXISTS getmountedvol;
@@ -455 +453 @@
     FOREIGN KEY(parent_id) REFERENCES directory(dir_id),
     PRIMARY KEY(dir_id),
-    KEY(parent_id)
+    KEY(parent_id),
+    KEY(dirname),
+    UNIQUE(dirname, parent_id)
 ) ENGINE=innodb DEFAULT CHARSET=latin1;
 
@@ -466 +466 @@
 CREATE TABLE storage_object (
     so_id BIGINT NOT NULL AUTO_INCREMENT,
-    ext_id VARCHAR(255) NOT NULL UNIQUE,
+    ext_id VARCHAR(255) NOT NULL,
     ext_id_basename VARCHAR(255) NOT NULL,
     dir_id BIGINT NOT NULL,
@@ -472 +472 @@
     type enum('REG_FILE'),
     PRIMARY KEY(so_id),
+    UNIQUE KEY(ext_id),
     KEY(dir_id),
     KEY(type)
@@ -514 +515 @@
 CREATE TABLE volume (
     vol_id INT NOT NULL AUTO_INCREMENT,
-    name VARCHAR(255) UNIQUE NOT NULL,
+    name VARCHAR(255) NOT NULL,
     host VARCHAR(255) NOT NULL,
     path VARCHAR(255) NOT NULL,
@@ -520 +521 @@
     available BOOLEAN DEFAULT FALSE,
     xattr BOOLEAN DEFAULT FALSE,
+    mountpoint VARCHAR(255) NOT NULL,
     PRIMARY KEY(vol_id),
+    UNIQUE KEY(name),
+    UNiQUE KEY(path),
     KEY(host(16)),
-    KEY(path(255)),
     KEY(allocate),
-    KEY(available)
+    KEY(available),
+    KEY(mountpoint(255))
+) ENGINE=innodb DEFAULT CHARSET=latin1;
+
+###
+
+CREATE TABLE mountedvol(
+    vol_id INT NOT NULL,
+    FOREIGN KEY(vol_id) REFERENCES volume(vol_id) ON DELETE CASCADE,
+    name VARCHAR(255) NOT NULL,
+    host VARCHAR(255) NOT NULL,
+    path VARCHAR(255) NOT NULL,
+    FOREIGN KEY(path) REFERENCES volume(path) ON DELETE CASCADE,
+    allocate BOOLEAN DEFAULT FALSE,
+    available BOOLEAN DEFAULT FALSE,
+    xattr BOOLEAN DEFAULT FALSE,
+    mountpoint VARCHAR(255) NOT NULL,
+    FOREIGN KEY(mountpoint) REFERENCES volume(mountpoint) ON DELETE CASCADE,
+    total BIGINT NOT NULL,
+    used BIGINT NOT NULL,
+    PRIMARY KEY(vol_id),
+    KEY(name),
+    KEY(host),
+    KEY(path),
+    KEY(allocate),
+    KEY(available),
+    KEY(xattr),
+    KEY(mountpoint(255))
 ) ENGINE=innodb DEFAULT CHARSET=latin1;
 
@@ -546 +576 @@
 ###
 
-CREATE TABLE mount (
-    mountpoint VARCHAR(255) NOT NULL,
-    total BIGINT NOT NULL,
-    used BIGINT NOT NULL,
-    PRIMARY KEY(mountpoint)
-) ENGINE=innodb DEFAULT CHARSET=latin1;
-
-###
-
 CREATE TABLE log (
     timestamp TIMESTAMP,
@@ -562 +583 @@
     message VARCHAR(2048) NOT NULL,
     PRIMARY KEY(timestamp)
-) ENGINE=innodb DEFAULT CHARSET=latin1;
-
-###
-
-CREATE TABLE mountedvol(
-    mountpoint VARCHAR(255) NOT NULL,
-    FOREIGN KEY(mountpoint) REFERENCES mount(mountpoint) ON DELETE CASCADE,
-    total BIGINT NOT NULL,
-    used BIGINT NOT NULL,
-    vol_id INT NOT NULL,
-    FOREIGN KEY(vol_id) REFERENCES volume(vol_id) ON DELETE CASCADE,
-    name VARCHAR(255) NOT NULL,
-    host VARCHAR(255) NOT NULL,
-    path VARCHAR(255) NOT NULL,
-    FOREIGN KEY(path) REFERENCES volume(path) ON DELETE CASCADE,
-    allocate BOOLEAN DEFAULT FALSE,
-    available BOOLEAN DEFAULT FALSE,
-    xattr BOOLEAN DEFAULT FALSE,
-    PRIMARY KEY(mountpoint),
-    KEY(vol_id),
-    KEY(name),
-    KEY(host),
-    KEY(path),
-    KEY(allocate),
-    KEY(available),
-    KEY(xattr)
 ) ENGINE=innodb DEFAULT CHARSET=latin1;
 
@@ -608 +603 @@
 
     -- store the okey checking state
-    SELECT @@FOREIGN_KEY_CHECKS INTO key_checks; 
+--    SELECT @@FOREIGN_KEY_CHECKS INTO key_checks; 
     -- disable foregin check checks to prevent deadlocks on the mountedvol table
-    SET FOREIGN_KEY_CHECKS=0;
+--    SET FOREIGN_KEY_CHECKS=0;
 
     -- make sure the temp table does not already exist... this can happy if the
@@ -629 +624 @@
     OPEN cur1;
 
-    DELETE FROM mountedvol;
-
     myloop: LOOP
         FETCH cur1 INTO vol_idvar, namevar, hostvar, pathvar, allocatevar, availablevar, xattrvar;
         IF `done` THEN LEAVE myloop; END IF;
-        INSERT INTO mountedvol
+        REPLACE INTO mountedvol
             SELECT mountpoint, total, used, vol_idvar, namevar, hostvar, pathvar, allocatevar, availablevar, xattrvar
             FROM
@@ -650 +643 @@
 
     -- restore the original key checking state
-    SET @@FOREIGN_KEY_CHECKS = key_checks; 
+--    SET @@FOREIGN_KEY_CHECKS = key_checks; 
 
     DROP TABLE IF EXISTS myvolume;
 
-    SET FOREIGN_KEY_CHECKS=1;
+--    SET FOREIGN_KEY_CHECKS=1;
 
     COMMIT;