Changeset 25027 for branches/pap/Nebulous-Server/lib/Nebulous/Server/SQL.pm
- Timestamp:
- Aug 7, 2009, 4:08:25 PM (17 years ago)
- Location:
- branches/pap
- Files:
-
- 3 edited
-
. (modified) (1 prop)
-
Nebulous-Server (modified) (1 prop)
-
Nebulous-Server/lib/Nebulous/Server/SQL.pm (modified) (19 diffs)
Legend:
- Unmodified
- Added
- Removed
-
branches/pap
- Property svn:mergeinfo changed
-
branches/pap/Nebulous-Server
- Property svn:mergeinfo changed
-
branches/pap/Nebulous-Server/lib/Nebulous/Server/SQL.pm
r23947 r25027 67 67 WHERE parent_id = ? 68 68 AND dirname = ? 69 LOCK IN SHARE MODE 69 70 }, 70 71 new_directory => qq{ … … 82 83 stat_object => qq{ 83 84 SELECT 84 storage_object.so_id, 85 storage_object.ext_id, 86 read_lock, 87 write_lock, 88 storage_object_attr.epoch, 89 storage_object_attr.mtime, 85 so.so_id, 86 so.ext_id, 87 attr.read_lock, 88 attr.write_lock, 89 attr.epoch, 90 attr.mtime, 91 SUM(available) as available, 90 92 COUNT(instance.so_id) as instances 91 FROM storage_object 92 JOIN storage_object_attr 93 FROM storage_object as so 94 JOIN storage_object_attr as attr 93 95 USING (so_id) 94 96 JOIN instance 95 97 USING (so_id) 96 WHERE ext_id = ? 97 GROUP BY storage_object.so_id 98 LEFT JOIN mountedvol 99 USING(vol_id) 100 WHERE ext_id = ? 101 GROUP BY so.so_id 98 102 }, 99 103 # Note: this sets an update lock … … 175 179 WHERE ext_id = ? 176 180 }, 177 delete_instance 181 delete_instance_by_ins_id => qq{ 178 182 DELETE FROM instance 179 WHERE uri= ?183 WHERE ins_id = ? 180 184 }, 181 185 get_object_from_uri => qq{ … … 184 188 WHERE uri = ? 185 189 }, 190 get_instance_by_uri => qq{ 191 SELECT ins_id 192 FROM instance 193 WHERE 194 so_id = ? 195 AND uri = ? 196 }, 186 197 get_instance_count => qq{ 187 198 SELECT count(ins_id) 188 199 FROM instance 189 200 WHERE so_id = ? 201 }, 202 get_instance_count_by_ext_id => qq{ 203 SELECT 204 so_id, 205 count(ins_id) as total, 206 sum(available) as available 207 FROM instance 208 JOIN storage_object 209 USING(so_id) 210 LEFT JOIN mountedvol 211 USING(vol_id) 212 WHERE 213 ext_id = ? 214 GROUP BY so_id 215 }, 216 copy_dead_instances_to_deleted => qq{ 217 INSERT INTO deleted 218 SELECT vol_id, uri, NULL 219 FROM instance 220 LEFT JOIN mountedvol 221 USING(vol_id) 222 WHERE 223 so_id = ? 224 AND (mountedvol.available IS NULL || mountedvol.available = 0) 225 }, 226 find_dead_instances_by_so_id => qq{ 227 SELECT so_id, ins_id, vol_id, uri 228 FROM instance 229 LEFT JOIN mountedvol 230 USING(vol_id) 231 WHERE 232 so_id = ? 233 AND (mountedvol.available IS NULL || mountedvol.available = 0) 190 234 }, 191 235 get_object_instances => qq{ … … 233 277 get_replication_volume_for_ext_id => qq{ 234 278 SELECT 235 vol_id,279 m.vol_id, 236 280 host, 237 281 path, 238 282 xattr, 239 283 total - used as free 240 FROM ( 241 -- This query works but is slow... 242 -- SELECT 243 -- mountedvol.* 244 -- FROM mountedvol 245 -- WHERE 246 -- vol_id NOT IN( 247 -- SELECT vol_id 248 -- FROM storage_object 249 -- JOIN instance 250 -- USING(so_id) 251 -- WHERE ext_id = -- 252 -- ) 253 SELECT 254 m.* 255 FROM mountedvol AS m 256 LEFT JOIN instance AS i 257 ON m.vol_id = i.vol_id 258 AND i.so_id = ( 259 SELECT so_id 260 FROM storage_object 261 WHERE ext_id = ? 262 ) 263 WHERE 264 i.vol_id IS NULL 265 ) as Foo 266 WHERE 267 used / total < ? 284 FROM mountedvol AS m 285 LEFT JOIN instance AS i 286 ON m.vol_id = i.vol_id 287 AND i.so_id = ( 288 SELECT so_id 289 FROM storage_object 290 WHERE ext_id = ? 291 ) 292 WHERE 293 i.vol_id IS NULL 294 AND used / total < ? 268 295 AND available = ? 269 296 AND allocate = ? 270 ORDER BY RAND()271 LIMIT 1297 ORDER BY RAND() 298 LIMIT 1 272 299 }, 273 300 get_storage_volume => qq{ … … 287 314 }, 288 315 new_volume => qq{ 289 INSERT INTO volume (name, host, path, allocate, available, xattr )290 VALUES (?, ?, ?, TRUE, TRUE, FALSE )316 INSERT INTO volume (name, host, path, allocate, available, xattr, mountpoint) 317 VALUES (?, ?, ?, TRUE, TRUE, FALSE, ?) 291 318 }, 292 319 get_volume_by_name => qq{ … … 310 337 }, 311 338 find_object_by_ext_id => qq{ 312 SELECT ext_id, ext_id_basename339 SELECT so_id, ext_id, ext_id_basename 313 340 FROM storage_object 314 341 WHERE ext_id = ? … … 318 345 FROM storage_object 319 346 WHERE dir_id = ? 347 }, 348 find_dir_by_parent_id => qq{ 349 SELECT dir_id, dirname 350 FROM directory 351 WHERE parent_id = ? 320 352 }, 321 353 rename_object => qq{ … … 397 429 }, 398 430 get_mounted_volumes => qq{ 399 SELECT *FROM mountedvol ORDER BY host, name431 SELECT mountpoint, total, used, vol_id, name, host, path, allocate, available, xattr FROM mountedvol ORDER BY host, name 400 432 }, 401 433 ); … … 424 456 DROP TABLE IF EXISTS lock_record; 425 457 DROP TABLE IF EXISTS volume; 426 DROP TABLE IF EXISTS mount ;458 DROP TABLE IF EXISTS mountedvol; 427 459 DROP TABLE IF EXISTS log; 428 DROP TABLE IF EXISTS mountedvol;429 460 DROP TABLE IF EXISTS directory; 430 DROP PROCEDURE IF EXISTS getmountedvol;461 DROP TABLE IF EXISTS deleted; 431 462 SET FOREIGN_KEY_CHECKS=1 432 463 END 464 #DROP PROCEDURE IF EXISTS getmountedvol; 433 465 $sql{get_db_clear} = \@clear; 434 466 } … … 447 479 448 480 1; 481 482 # ### 483 # 484 # CREATE PROCEDURE getmountedvol() DETERMINISTIC 485 # BEGIN 486 # DECLARE done BOOLEAN DEFAULT FALSE; 487 # DECLARE vol_idvar INT; 488 # DECLARE namevar VARCHAR(255); 489 # DECLARE hostvar VARCHAR(255); 490 # DECLARE pathvar VARCHAR(255); 491 # DECLARE allocatevar BOOLEAN; 492 # DECLARE availablevar BOOLEAN; 493 # DECLARE xattrvar BOOLEAN; 494 # DECLARE trans_level VARCHAR(255); 495 # DECLARE key_checks BOOLEAN; 496 # DECLARE cur1 CURSOR FOR SELECT vol_id, name, host, path, allocate, available, xattr FROM myvolume; 497 # DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE; 498 # 499 # -- store the okey checking state 500 # -- SELECT @@FOREIGN_KEY_CHECKS INTO key_checks; 501 # -- disable foregin check checks to prevent deadlocks on the mountedvol table 502 # -- SET FOREIGN_KEY_CHECKS=0; 503 # 504 # -- make sure the temp table does not already exist... this can happy if the 505 # -- stored proc fails for some reason 506 # DROP TABLE IF EXISTS myvolume; 507 # CREATE TEMPORARY TABLE myvolume LIKE volume; 508 # INSERT INTO myvolume SELECT * FROM volume; 509 # 510 # -- store the current transaction level 511 # -- SELECT @@session.tx_isolation INTO trans_level; 512 # -- set trans level to repeatable-read so the volume table does not change 513 # -- out from under our cursor 514 # -- SET @@session.tx_isolation = 'REPEATABLE-READ'; 515 # 516 # -- iterate over the volume table finding the coresponding entry in the 517 # -- mount table and inserting union of the volume & mount row into the 518 # -- mountedvol table 519 # OPEN cur1; 520 # 521 # myloop: LOOP 522 # FETCH cur1 INTO vol_idvar, namevar, hostvar, pathvar, allocatevar, availablevar, xattrvar; 523 # IF `done` THEN LEAVE myloop; END IF; 524 # REPLACE INTO mountedvol 525 # SELECT mountpoint, total, used, vol_idvar, namevar, hostvar, pathvar, allocatevar, availablevar, xattrvar 526 # FROM 527 # (SELECT *, INSTR(pathvar, mountpoint) = 1 as substring 528 # FROM mount 529 # HAVING substring = 1 530 # ORDER BY substring DESC, LENGTH(mountpoint) DESC 531 # LIMIT 1) as bar; 532 # END LOOP myloop; 533 # 534 # CLOSE cur1; 535 # 536 # -- restore the original transaction level 537 # -- SET @@session.tx_isolation = trans_level; 538 # 539 # -- restore the original key checking state 540 # -- SET @@FOREIGN_KEY_CHECKS = key_checks; 541 # 542 # DROP TABLE IF EXISTS myvolume; 543 # 544 # -- SET FOREIGN_KEY_CHECKS=1; 545 # 546 # COMMIT; 547 # END 449 548 450 549 __DATA__ … … 455 554 FOREIGN KEY(parent_id) REFERENCES directory(dir_id), 456 555 PRIMARY KEY(dir_id), 457 KEY(parent_id) 556 KEY(parent_id), 557 KEY(dirname), 558 UNIQUE(dirname, parent_id) 458 559 ) ENGINE=innodb DEFAULT CHARSET=latin1; 459 560 … … 466 567 CREATE TABLE storage_object ( 467 568 so_id BIGINT NOT NULL AUTO_INCREMENT, 468 ext_id VARCHAR(255) NOT NULL UNIQUE,569 ext_id VARCHAR(255) NOT NULL, 469 570 ext_id_basename VARCHAR(255) NOT NULL, 470 571 dir_id BIGINT NOT NULL, … … 472 573 type enum('REG_FILE'), 473 574 PRIMARY KEY(so_id), 575 UNIQUE KEY(ext_id), 474 576 KEY(dir_id), 475 577 KEY(type) … … 514 616 CREATE TABLE volume ( 515 617 vol_id INT NOT NULL AUTO_INCREMENT, 516 name VARCHAR(255) UNIQUENOT NULL,618 name VARCHAR(255) NOT NULL, 517 619 host VARCHAR(255) NOT NULL, 518 620 path VARCHAR(255) NOT NULL, … … 520 622 available BOOLEAN DEFAULT FALSE, 521 623 xattr BOOLEAN DEFAULT FALSE, 624 mountpoint VARCHAR(255) NOT NULL, 522 625 PRIMARY KEY(vol_id), 626 UNIQUE KEY(name), 627 UNiQUE KEY(path), 523 628 KEY(host(16)), 524 KEY(path(255)),525 629 KEY(allocate), 526 KEY(available) 630 KEY(available), 631 KEY(mountpoint(255)) 632 ) ENGINE=innodb DEFAULT CHARSET=latin1; 633 634 ### 635 636 CREATE TABLE mountedvol( 637 vol_id INT NOT NULL, 638 FOREIGN KEY(vol_id) REFERENCES volume(vol_id) ON DELETE CASCADE, 639 name VARCHAR(255) NOT NULL, 640 host VARCHAR(255) NOT NULL, 641 path VARCHAR(255) NOT NULL, 642 FOREIGN KEY(path) REFERENCES volume(path) ON DELETE CASCADE, 643 allocate BOOLEAN DEFAULT FALSE, 644 available BOOLEAN DEFAULT FALSE, 645 xattr BOOLEAN DEFAULT FALSE, 646 mountpoint VARCHAR(255) NOT NULL, 647 FOREIGN KEY(mountpoint) REFERENCES volume(mountpoint) ON DELETE CASCADE, 648 total BIGINT NOT NULL, 649 used BIGINT NOT NULL, 650 PRIMARY KEY(vol_id), 651 KEY(name), 652 KEY(host), 653 KEY(path), 654 KEY(allocate), 655 KEY(available), 656 KEY(xattr), 657 KEY(mountpoint(255)) 527 658 ) ENGINE=innodb DEFAULT CHARSET=latin1; 528 659 … … 532 663 ins_id BIGINT NOT NULL AUTO_INCREMENT, 533 664 so_id BIGINT NOT NULL, 534 FOREIGN KEY(so_id) REFERENCES storage_object(so_id) ,665 FOREIGN KEY(so_id) REFERENCES storage_object(so_id) ON DELETE CASCADE, 535 666 vol_id INT NOT NULL, 536 667 FOREIGN KEY(vol_id) REFERENCES volume(vol_id), … … 546 677 ### 547 678 548 CREATE TABLE mount (549 mountpoint VARCHAR(255) NOT NULL,550 total BIGINT NOT NULL,551 used BIGINT NOT NULL,552 PRIMARY KEY(mountpoint)553 ) ENGINE=innodb DEFAULT CHARSET=latin1;554 555 ###556 557 679 CREATE TABLE log ( 558 680 timestamp TIMESTAMP, … … 566 688 ### 567 689 568 CREATE TABLE mountedvol( 569 mountpoint VARCHAR(255) NOT NULL, 570 FOREIGN KEY(mountpoint) REFERENCES mount(mountpoint) ON DELETE CASCADE, 571 total BIGINT NOT NULL, 572 used BIGINT NOT NULL, 690 CREATE TABLE deleted ( 573 691 vol_id INT NOT NULL, 574 FOREIGN KEY(vol_id) REFERENCES volume(vol_id) ON DELETE CASCADE, 575 name VARCHAR(255) NOT NULL, 576 host VARCHAR(255) NOT NULL, 577 path VARCHAR(255) NOT NULL, 578 FOREIGN KEY(path) REFERENCES volume(path) ON DELETE CASCADE, 579 allocate BOOLEAN DEFAULT FALSE, 580 available BOOLEAN DEFAULT FALSE, 581 xattr BOOLEAN DEFAULT FALSE, 582 PRIMARY KEY(mountpoint), 692 FOREIGN KEY(vol_id) REFERENCES volume(vol_id), 693 uri VARCHAR(255) NOT NULL, 694 timestamp TIMESTAMP, 695 PRIMARY KEY(vol_id, uri), 583 696 KEY(vol_id), 584 KEY(name), 585 KEY(host), 586 KEY(path), 587 KEY(allocate), 588 KEY(available), 589 KEY(xattr) 590 ) ENGINE=innodb DEFAULT CHARSET=latin1; 591 592 ### 593 594 CREATE PROCEDURE getmountedvol() DETERMINISTIC 595 BEGIN 596 DECLARE done BOOLEAN DEFAULT FALSE; 597 DECLARE vol_idvar INT; 598 DECLARE namevar VARCHAR(255); 599 DECLARE hostvar VARCHAR(255); 600 DECLARE pathvar VARCHAR(255); 601 DECLARE allocatevar BOOLEAN; 602 DECLARE availablevar BOOLEAN; 603 DECLARE xattrvar BOOLEAN; 604 DECLARE trans_level VARCHAR(255); 605 DECLARE key_checks BOOLEAN; 606 DECLARE cur1 CURSOR FOR SELECT vol_id, name, host, path, allocate, available, xattr FROM myvolume; 607 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE; 608 609 -- store the okey checking state 610 SELECT @@FOREIGN_KEY_CHECKS INTO key_checks; 611 -- disable foregin check checks to prevent deadlocks on the mountedvol table 612 SET FOREIGN_KEY_CHECKS=0; 613 614 -- make sure the temp table does not already exist... this can happy if the 615 -- stored proc fails for some reason 616 DROP TABLE IF EXISTS myvolume; 617 CREATE TEMPORARY TABLE myvolume LIKE volume; 618 INSERT INTO myvolume SELECT * FROM volume; 619 620 -- store the current transaction level 621 -- SELECT @@session.tx_isolation INTO trans_level; 622 -- set trans level to repeatable-read so the volume table does not change 623 -- out from under our cursor 624 -- SET @@session.tx_isolation = 'REPEATABLE-READ'; 625 626 -- iterate over the volume table finding the coresponding entry in the 627 -- mount table and inserting union of the volume & mount row into the 628 -- mountedvol table 629 OPEN cur1; 630 631 DELETE FROM mountedvol; 632 633 myloop: LOOP 634 FETCH cur1 INTO vol_idvar, namevar, hostvar, pathvar, allocatevar, availablevar, xattrvar; 635 IF `done` THEN LEAVE myloop; END IF; 636 INSERT INTO mountedvol 637 SELECT mountpoint, total, used, vol_idvar, namevar, hostvar, pathvar, allocatevar, availablevar, xattrvar 638 FROM 639 (SELECT *, INSTR(pathvar, mountpoint) = 1 as substring 640 FROM mount 641 HAVING substring = 1 642 ORDER BY substring DESC, LENGTH(mountpoint) DESC 643 LIMIT 1) as bar; 644 END LOOP myloop; 645 646 CLOSE cur1; 647 648 -- restore the original transaction level 649 -- SET @@session.tx_isolation = trans_level; 650 651 -- restore the original key checking state 652 SET @@FOREIGN_KEY_CHECKS = key_checks; 653 654 DROP TABLE IF EXISTS myvolume; 655 656 SET FOREIGN_KEY_CHECKS=1; 657 658 COMMIT; 659 END 697 KEY(uri) 698 ) ENGINE=innodb DEFAULT CHARSET=latin1;
Note:
See TracChangeset
for help on using the changeset viewer.
