Changeset 24637 for trunk/Nebulous-Server/lib/Nebulous/Server/SQL.pm

Timestamp:

Jul 1, 2009, 5:21:32 PM (17 years ago)

Author:

jhoblitt

Message:

add the ability to delete a storage object when it has instances that are offline

File:

• trunk/Nebulous-Server/lib/Nebulous/Server/SQL.pm (modified) (6 diffs)

trunk/Nebulous-Server/lib/Nebulous/Server/SQL.pm

@@ -180 +180 @@
         WHERE ins_id = ?
     },
+    delete_instance_by_so_id => qq{
+        DELETE FROM instance
+        WHERE so_id = ?
+    },
     get_object_from_uri   => qq{
         SELECT so_id
@@ -198 +202 @@
     },
     get_instance_count_by_ext_id   => qq{
-        SELECT count(ins_id), so_id
+        SELECT 
+            so_id,
+            count(ins_id) as total,
+            sum(available) as available
         FROM instance
         JOIN storage_object
             USING(so_id)
+        LEFT JOIN mountedvol
+            USING(vol_id)
         WHERE
             ext_id = ?
         GROUP BY so_id
+    },
+    copy_instances_to_deleted => qq{
+        INSERT INTO deleted 
+        SELECT NULL, vol_id, uri
+        FROM instance
+        WHERE
+            so_id = ?
     },
     get_object_instances    => qq{
@@ -432 +448 @@
 DROP TABLE IF EXISTS log;
 DROP TABLE IF EXISTS directory;
-DROP PROCEDURE IF EXISTS getmountedvol;
+DROP TABLE IF EXISTS deleted;
 SET FOREIGN_KEY_CHECKS=1
 END
+#DROP PROCEDURE IF EXISTS getmountedvol;
     $sql{get_db_clear} = \@clear;
 }
@@ -451 +468 @@
 
 1;
+
+# ###
+# 
+# CREATE PROCEDURE getmountedvol() DETERMINISTIC
+# BEGIN
+#     DECLARE done BOOLEAN DEFAULT FALSE;
+#     DECLARE vol_idvar INT;
+#     DECLARE namevar VARCHAR(255);
+#     DECLARE hostvar VARCHAR(255);
+#     DECLARE pathvar VARCHAR(255);
+#     DECLARE allocatevar BOOLEAN;
+#     DECLARE availablevar BOOLEAN;
+#     DECLARE xattrvar BOOLEAN;
+#     DECLARE trans_level VARCHAR(255);
+#     DECLARE key_checks BOOLEAN;
+#     DECLARE cur1 CURSOR FOR SELECT vol_id, name, host, path, allocate, available, xattr FROM myvolume;
+#     DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE;
+# 
+#     -- store the okey checking state
+# --    SELECT @@FOREIGN_KEY_CHECKS INTO key_checks; 
+#     -- disable foregin check checks to prevent deadlocks on the mountedvol table
+# --    SET FOREIGN_KEY_CHECKS=0;
+# 
+#     -- make sure the temp table does not already exist... this can happy if the
+#     -- stored proc fails for some reason
+#     DROP TABLE IF EXISTS myvolume;
+#     CREATE TEMPORARY TABLE myvolume LIKE volume;
+#     INSERT INTO myvolume SELECT * FROM volume;
+# 
+#     -- store the current transaction level
+# --    SELECT @@session.tx_isolation INTO trans_level; 
+#     -- set trans level to repeatable-read so the volume table does not change
+#     -- out from under our cursor
+# --    SET @@session.tx_isolation = 'REPEATABLE-READ';
+# 
+#     -- iterate over the volume table finding the coresponding entry in the
+#     -- mount table and inserting union of the volume & mount row into the
+#     -- mountedvol table
+#     OPEN cur1;
+# 
+#     myloop: LOOP
+#         FETCH cur1 INTO vol_idvar, namevar, hostvar, pathvar, allocatevar, availablevar, xattrvar;
+#         IF `done` THEN LEAVE myloop; END IF;
+#         REPLACE INTO mountedvol
+#             SELECT mountpoint, total, used, vol_idvar, namevar, hostvar, pathvar, allocatevar, availablevar, xattrvar
+#             FROM
+#                 (SELECT *, INSTR(pathvar, mountpoint) = 1 as substring
+#                 FROM mount
+#                 HAVING substring = 1
+#                 ORDER BY substring DESC, LENGTH(mountpoint) DESC
+#                 LIMIT 1) as bar;
+#     END LOOP myloop;
+#     
+#     CLOSE cur1;
+# 
+#     -- restore the original transaction level
+# --    SET @@session.tx_isolation = trans_level;
+# 
+#     -- restore the original key checking state
+# --    SET @@FOREIGN_KEY_CHECKS = key_checks; 
+# 
+#     DROP TABLE IF EXISTS myvolume;
+# 
+# --    SET FOREIGN_KEY_CHECKS=1;
+# 
+#     COMMIT;
+# END 
 
 __DATA__
@@ -568 +652 @@
     ins_id BIGINT NOT NULL AUTO_INCREMENT,
     so_id BIGINT NOT NULL,
-    FOREIGN KEY(so_id) REFERENCES storage_object(so_id),
+    FOREIGN KEY(so_id) REFERENCES storage_object(so_id) ON DELETE CASCADE,
     vol_id INT NOT NULL,
     FOREIGN KEY(vol_id) REFERENCES volume(vol_id),
@@ -593 +677 @@
 ###
 
-CREATE PROCEDURE getmountedvol() DETERMINISTIC
-BEGIN
-    DECLARE done BOOLEAN DEFAULT FALSE;
-    DECLARE vol_idvar INT;
-    DECLARE namevar VARCHAR(255);
-    DECLARE hostvar VARCHAR(255);
-    DECLARE pathvar VARCHAR(255);
-    DECLARE allocatevar BOOLEAN;
-    DECLARE availablevar BOOLEAN;
-    DECLARE xattrvar BOOLEAN;
-    DECLARE trans_level VARCHAR(255);
-    DECLARE key_checks BOOLEAN;
-    DECLARE cur1 CURSOR FOR SELECT vol_id, name, host, path, allocate, available, xattr FROM myvolume;
-    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE;
-
-    -- store the okey checking state
---    SELECT @@FOREIGN_KEY_CHECKS INTO key_checks; 
-    -- disable foregin check checks to prevent deadlocks on the mountedvol table
---    SET FOREIGN_KEY_CHECKS=0;
-
-    -- make sure the temp table does not already exist... this can happy if the
-    -- stored proc fails for some reason
-    DROP TABLE IF EXISTS myvolume;
-    CREATE TEMPORARY TABLE myvolume LIKE volume;
-    INSERT INTO myvolume SELECT * FROM volume;
-
-    -- store the current transaction level
---    SELECT @@session.tx_isolation INTO trans_level; 
-    -- set trans level to repeatable-read so the volume table does not change
-    -- out from under our cursor
---    SET @@session.tx_isolation = 'REPEATABLE-READ';
-
-    -- iterate over the volume table finding the coresponding entry in the
-    -- mount table and inserting union of the volume & mount row into the
-    -- mountedvol table
-    OPEN cur1;
-
-    myloop: LOOP
-        FETCH cur1 INTO vol_idvar, namevar, hostvar, pathvar, allocatevar, availablevar, xattrvar;
-        IF `done` THEN LEAVE myloop; END IF;
-        REPLACE INTO mountedvol
-            SELECT mountpoint, total, used, vol_idvar, namevar, hostvar, pathvar, allocatevar, availablevar, xattrvar
-            FROM
-                (SELECT *, INSTR(pathvar, mountpoint) = 1 as substring
-                FROM mount
-                HAVING substring = 1
-                ORDER BY substring DESC, LENGTH(mountpoint) DESC
-                LIMIT 1) as bar;
-    END LOOP myloop;
-    
-    CLOSE cur1;
-
-    -- restore the original transaction level
---    SET @@session.tx_isolation = trans_level;
-
-    -- restore the original key checking state
---    SET @@FOREIGN_KEY_CHECKS = key_checks; 
-
-    DROP TABLE IF EXISTS myvolume;
-
---    SET FOREIGN_KEY_CHECKS=1;
-
-    COMMIT;
-END 
+CREATE TABLE deleted (
+    timestamp TIMESTAMP,
+    vol_id INT NOT NULL,
+    uri VARCHAR(255) NOT NULL,
+    PRIMARY KEY(timestamp),
+    KEY(vol_id),
+    KEY(uri)
+) ENGINE=innodb DEFAULT CHARSET=latin1;