/home/Śpiechu->Blog

CREATE TABLE `galleries` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  `title` VARCHAR(250) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;
 
CREATE TABLE IF NOT EXISTS `images` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `gallery_id` INT(10) UNSIGNED NOT NULL,
  `filename` VARCHAR(50) NOT NULL,
  `ordr` INT(10) UNSIGNED NOT NULL DEFAULT '1',
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `image_to_gallery` (`gallery_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ;

DELIMITER $$
CREATE TRIGGER `updated_current_timestamp` BEFORE UPDATE ON `galleries`
   FOR EACH ROW BEGIN
      SET NEW.updated = NOW();
END$$

ALTER TABLE `images`
   ADD CONSTRAINT `image_to_gallery` FOREIGN KEY (`gallery_id`) REFERENCES `galleries` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

$q = $pdo->prepare('INSERT INTO images (filename,gallery_id,ordr) (SELECT ?,?,MAX(ordr)+1 FROM images WHERE gallery_id=? LIMIT 1)');
$q->bindValue(1, 'obrazek.jpg', PDO::PARAM_STR);
$q->bindParam(2, $galleryId, PDO::PARAM_INT);
$q->bindParam(3, $galleryId, PDO::PARAM_INT);
$q->execute();

DELIMITER $$
CREATE PROCEDURE `insert_image`(IN image_filename VARCHAR(50), IN image_gallery_id INT, OUT last_inserted_id INT)
   MODIFIES SQL DATA
   COMMENT 'Inserts new image at the end of given gallery.'
   BEGIN
      DECLARE max_order INT;
 
      # Zamiast SET zmienna= uzywam SELECT INTO just FOR fun
      SELECT MAX(ordr) INTO max_order FROM images WHERE gallery_id=image_gallery_id LIMIT 1;
 
      # Gdy obrazek jest pierwszy w galerii
      IF max_order IS NULL THEN 
         SET max_order = 1;
      ELSE
         SET max_order = max_order + 1;
      END IF;
      INSERT INTO images (filename, gallery_id, ordr) VALUES (image_filename, image_gallery_id, max_order);
      SELECT LAST_INSERT_ID() INTO last_inserted_id;
END$$

$q = $pdo->prepare('CALL insert_image(?,?,@lastInsertId)');
$q->bindValue(1, 'obrazek.jpg', PDO::PARAM_STR);
$q->bindParam(2, $galleryId, PDO::PARAM_INT);
$q->execute();
$outputArray = $pdo->query('select @lastInsertId')->fetch(PDO::FETCH_ASSOC);
$lastInsertId = $outputArray['@lastInsertId'];

DELIMITER $$
CREATE PROCEDURE `delete_image`(IN image_id INT)
   MODIFIES SQL DATA
   COMMENT 'Deletes and reorders if there is a gap.'
   BEGIN
      DECLARE image_gallery_id, image_order, max_order INT;
      SELECT gallery_id, ordr INTO image_gallery_id, image_order FROM images WHERE id=image_id LIMIT 1;
      DELETE FROM images WHERE id=image_id LIMIT 1;
      SELECT MAX(ordr) INTO max_order FROM images WHERE gallery_id=image_gallery_id LIMIT 1;
 
      # Sprawdzamy czy istnieja jakies obrazki za skasowanym
      IF max_order IS NOT NULL AND max_order > image_order THEN
         WHILE image_order < max_order DO
 
            # Cofamy w petli wypelniajac luke po skasowanym obrazku
            UPDATE images SET ordr=image_order WHERE ordr=image_order+1 AND gallery_id=image_gallery_id;
            SET image_order = image_order + 1;
       END WHILE;
   END IF;
END$$

$q = $pdo->prepare('CALL delete_image(?)');
$q->bindParam(1, $imageId, PDO::PARAM_INT);
$q->execute();

DELIMITER $$
CREATE PROCEDURE `reorder_image`(IN image_id INT, IN new_image_order INT)
   MODIFIES SQL DATA
   COMMENT 'Reorders images. Does nothing if given order is out of scope.'
   BEGIN
      DECLARE current_image_order, image_gallery_id, is_destination_order_exists INT;
 
      # Lapie obecne polozenie obrazka i przy okazji id galerii
      SELECT ordr, gallery_id INTO current_image_order, image_gallery_id FROM images WHERE id=image_id LIMIT 1;
 
      # Sprawdzam czy punkt docelowy w ogole istnieje
      SELECT ordr INTO is_destination_order_exists FROM images WHERE gallery_id=image_gallery_id AND ordr=new_image_order LIMIT 1;
 
      # Jezeli punkt docelowy istnieje i jest inny od obecnego TO rozpoczynam dzialanie
      IF is_destination_order_exists IS NOT NULL AND current_image_order <> new_image_order THEN
 
         # Jezeli przestawiam obrazek do gory
         IF current_image_order >  new_image_order THEN 
            WHILE current_image_order >=  new_image_order DO
               UPDATE images SET ordr=current_image_order+1 WHERE gallery_id=image_gallery_id AND ordr= current_image_order LIMIT 1;
               SET current_image_order = current_image_order - 1;
            END WHILE;
 
         # Jezeli przestawiam obrazek w dol
         ELSEIF  current_image_order <  new_image_order THEN 
            WHILE current_image_order <=  new_image_order DO
               UPDATE images SET ordr=current_image_order-1 WHERE gallery_id=image_gallery_id AND ordr= current_image_order LIMIT 1;
               SET current_image_order = current_image_order + 1;
            END WHILE;
         END IF;
 
         # Mam juz miejsce, wrzucam obrazek tam gdzie ma byc
         UPDATE images SET ordr=new_image_order WHERE id=image_id LIMIT 1;
      END IF;
END$$

$q = $pdo->prepare('CALL reorder_image(?,?)');
$q->bindParam(1, $imageId, PDO::PARAM_INT);
$q->bindParam(2, $wantedImageOrder, PDO::PARAM_INT);
$q->execute();