#
# MySQL upgrade script for upgrade database from version 2.0.23 to 2.0.24
#

 


#-----------------------------------------------------------------------------
#-- format_printer
#-----------------------------------------------------------------------------



CREATE TABLE `format_printer`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`format_id` INTEGER  NOT NULL,
	`printer_id` INTEGER  NOT NULL,
	`is_fixed_quantity` INTEGER default 0 NOT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY `unique_format_printer` (`format_id`, `printer_id`),
	CONSTRAINT `format_printer_FK_1`
		FOREIGN KEY (`format_id`)
		REFERENCES `format` (`id`)
		ON DELETE CASCADE,
	INDEX `format_printer_FI_2` (`printer_id`),
	CONSTRAINT `format_printer_FK_2`
		FOREIGN KEY (`printer_id`)
		REFERENCES `printer` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;

INSERT INTO format_printer (format_id, printer_id, is_fixed_quantity)
  SELECT DISTINCT printing_cost.format_id, printing_cost.printer_id, 0
  FROM printing_cost;



#-----------------------------------------------------------------------------
#-- format_printer_price
#-----------------------------------------------------------------------------



CREATE TABLE `format_printer_price`
(
	`id` INTEGER  NOT NULL AUTO_INCREMENT,
	`format_printer_id` INTEGER  NOT NULL,
	`minimum_quantity` INTEGER,
	`maximum_quantity` INTEGER,
	`price` FLOAT  NOT NULL,
	PRIMARY KEY (`id`),
	KEY `minimum_quantity_index`(`minimum_quantity`),
	KEY `maximum_quantity_index`(`maximum_quantity`),
	INDEX `format_printer_price_FI_1` (`format_printer_id`),
	CONSTRAINT `format_printer_price_FK_1`
		FOREIGN KEY (`format_printer_id`)
		REFERENCES `format_printer` (`id`)
		ON DELETE CASCADE
)Type=InnoDB;



INSERT INTO format_printer_price (format_printer_id, minimum_quantity, maximum_quantity, price)
  SELECT DISTINCT format_printer.id, printing_cost.minimum, printing_cost.maximum, printing_cost.unit_price
  FROM format_printer JOIN printing_cost ON (format_printer.format_id = printing_cost.format_id AND format_printer.printer_id = printing_cost.printer_id)
  ORDER BY format_printer.id, printing_cost.minimum;



#-----------------------------------------------------------------------------
#-- printing_cost
#-----------------------------------------------------------------------------

DROP TABLE printing_cost;


