JCL/sql/Scripts/jegatroncomponents_recreate.sql

/* Remove everything */


-- Remove all group detail tables
SET @tbls = (
SELECT GROUP_CONCAT(TABLE_NAME)
	FROM information_schema.TABLES t
	WHERE t.TABLE_SCHEMA = 'jegatroncomponents'
	AND TABLE_NAME LIKE 'group_details%');
-- SELECT @tbls;

delimiter //
BEGIN NOT ATOMIC
	IF @tbls IS NOT null THEN
		PREPARE stmt FROM concat('DROP TABLE ', @tbls);
		EXECUTE stmt;
		DEALLOCATE PREPARE stmt;
	END IF;
END;
//
delimiter ;


-- Remove all view that I know of
DROP VIEW IF EXISTS kicad_resistors;

-- Remove all functions and stored procedures
DROP PROCEDURE IF EXISTS `add_part_group`;
DROP PROCEDURE IF EXISTS `add_internal_part`;
DROP PROCEDURE IF EXISTS `add_manufacturer_part`;
DROP FUNCTION IF EXISTS `to_value_string`;
DROP FUNCTION IF EXISTS `to_pn_string`;

-- Remove all tables
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `qualified_parts`;
DROP TABLE IF EXISTS `part_group_properties`;
DROP TABLE IF EXISTS `internal_parts`;
DROP TABLE IF EXISTS `part_groups`;
DROP TABLE IF EXISTS `manufacturer_parts`;
DROP TABLE IF EXISTS `manufacturers`;
DROP TABLE IF EXISTS `supplier_part_numbers`;
DROP TABLE IF EXISTS `suppliers`;
DROP TABLE IF EXISTS `debug_msg`;
DROP TABLE IF EXISTS `component_packages`;
DROP TABLE IF EXISTS `component_symbols`;
DROP TABLE IF EXISTS `footprints_kicad`;
DROP TABLE IF EXISTS `symbols_kicad`;
SET FOREIGN_KEY_CHECKS=1;
-- Finished removing everything

/* Debug table to store whatever in */

CREATE TABLE `debug_msg` (
msg_id int PRIMARY KEY AUTO_INCREMENT,
msg text
);


/* Contains listing of part groups. Each group should have a dedicated
 * table containing part group specific details. */
CREATE TABLE `part_groups` (
  `part_group_id` varchar(50) NOT NULL,
  `part_group_detail_table` varchar(50) DEFAULT NULL,
  `group_display_name` varchar(50) DEFAULT NULL,
  `group_description` text DEFAULT NULL,
  PRIMARY KEY (`part_group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

/* Helper function for adding component categories (groups)
 * It creates a row in part_group table and a corresponding new table */
DELIMITER //
CREATE OR REPLACE PROCEDURE `add_part_group`(
 IN group_name varchar(50),
 IN group_display_name varchar(50),
 IN group_description text)
BEGIN
	SET @table_name := concat('group_details_', group_name);

	INSERT INTO `part_groups` (`part_group_id`, `group_display_name`, `group_description`, `part_group_detail_table`) VALUES (group_name, group_display_name, group_description, @table_name);

	SET @create_detail_table_query := concat('CREATE TABLE `', @table_name, '` (
		`detail_id` int NOT NULL AUTO_INCREMENT,
		PRIMARY KEY `', @table_name, '_PK` (`detail_id`),
		`internal_pn` int DEFAULT NULL,
		CONSTRAINT `', @table_name, '_part_FK` FOREIGN KEY (`internal_pn`) REFERENCES `internal_parts` (`internal_pn`) ON DELETE CASCADE,
		CONSTRAINT `', @table_name, '_part_AK` UNIQUE (`internal_pn`),
		`internal_pn_str` varchar(9) DEFAULT NULL,
		`mpn` varchar(50) DEFAULT NULL,
		`mfr` varchar(50) DEFAULT NULL,
		CONSTRAINT `', @table_name, '_mpn_FK` FOREIGN KEY (`mpn`, `mfr`) REFERENCES `manufacturer_parts` (`mpn`, `mfr`) ON DELETE CASCADE ON UPDATE CASCADE,
		CONSTRAINT `', @table_name, '_mpn_AK` UNIQUE (`mpn`, `mfr`)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
	');

	-- INSERT INTO `debug_msg`(`msg`) VALUES (@create_detail_table_query);

	EXECUTE IMMEDIATE @create_detail_table_query;

	SET @create_trigger_a := concat('
		CREATE OR REPLACE TRIGGER ', @table_name, '_add_pn_str
		BEFORE INSERT ON `', @table_name, '`
		FOR EACH ROW 
		 SET NEW.internal_pn_str := to_pn_string(NEW.internal_pn);');
	-- INSERT INTO `debug_msg`(`msg`) VALUES (@create_trigger_a);
	EXECUTE IMMEDIATE @create_trigger_a;

	SET @create_triggers_b := concat('
		CREATE OR REPLACE TRIGGER ', @table_name, '_update_pn_str
		BEFORE UPDATE ON `', @table_name, '`
		FOR EACH ROW 
		 SET NEW.internal_pn_str := to_pn_string(NEW.internal_pn);');
	-- INSERT INTO `debug_msg`(`msg`) VALUES (@create_triggers_b);
	EXECUTE IMMEDIATE @create_triggers_b;


	 
END;
//
DELIMITER ;


CREATE TABLE `internal_parts` (
  `internal_pn` int(11) NOT NULL AUTO_INCREMENT,
  `part_group_id` varchar(50) NOT NULL,
  `part_description` text DEFAULT NULL,
  `exclude_from_bom` tinyint(1) NOT NULL DEFAULT 0,
  `exclude_from_board` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`internal_pn`),
  KEY `internal_parts_group_id_fk` (`part_group_id`),
  CONSTRAINT `internal_parts_group_id_fk` FOREIGN KEY (`part_group_id`) REFERENCES `part_groups` (`part_group_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1062 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

ALTER TABLE `internal_parts` AUTO_INCREMENT=1000000;

# Helper function to convert int to a formatted part number string xx-xx-xxx
delimiter //
CREATE OR REPLACE FUNCTION to_pn_string(pn int) RETURNS varchar(9)
BEGIN
	DECLARE pnstrtmp varchar(7);
	DECLARE pnstr varchar(9);
	SET pnstrtmp := lpad(cast(pn AS char), 7, 0);
	SET pnstr := concat(substring(pnstrtmp, 1, 2), '-', substring(pnstrtmp, 3, 2), '-', substring(pnstrtmp, 5, 3));
	RETURN pnstr;
END
//
delimiter ;

CREATE TABLE `manufacturers` (
  `mfr` varchar(50) NOT NULL,
  `mfr_display_name` varchar(100) DEFAULT NULL,
  `url` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`mfr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `manufacturer_parts` (
  `mpn` varchar(50) NOT NULL,
  `mfr` varchar(50) NOT NULL,
  `part_group_id` varchar(50) NOT NULL,
  `part_description` text DEFAULT NULL,
  `lifecycle_status` enum('Active','NRND','Last Time Buy','Obsolete') NOT NULL,
  PRIMARY KEY (`mpn`,`mfr`),
  KEY `manufacturer_parts_mfr_FK` (`mfr`),
  KEY `manufacturer_parts_group_id_fk` (`part_group_id`),
  CONSTRAINT `manufacturer_parts_group_id_fk` FOREIGN KEY (`part_group_id`) REFERENCES `part_groups` (`part_group_id`),
  CONSTRAINT `manufacturer_parts_mfr_FK` FOREIGN KEY (`mfr`) REFERENCES `manufacturers` (`mfr`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `qualified_parts` (
  `internal_pn` int(11) NOT NULL,
  `mpn` varchar(50) NOT NULL,
  `mfr` varchar(50) NOT NULL,
  PRIMARY KEY (`internal_pn`,`mpn`,`mfr`),
  KEY `qualified_parts_FK_1` (`mpn`,`mfr`),
  CONSTRAINT `qualified_parts_FK` FOREIGN KEY (`internal_pn`) REFERENCES `internal_parts` (`internal_pn`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `qualified_parts_FK_1` FOREIGN KEY (`mpn`, `mfr`) REFERENCES `manufacturer_parts` (`mpn`, `mfr`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `suppliers` (
  `supplier` varchar(100) NOT NULL,
  `url` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`supplier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `supplier_part_numbers` (
  `supplier_pno` varchar(100) NOT NULL,
  `supplier` varchar(100) NOT NULL,
  `mpn` varchar(50) NOT NULL,
  `mfr` varchar(100) NOT NULL,
  `url` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`supplier_pno`,`supplier`),
  KEY `supplier_part_numbers_FK_1` (`supplier`),
  KEY `supplier_part_numbers_FK` (`mpn`,`mfr`),
  CONSTRAINT `supplier_part_numbers_FK` FOREIGN KEY (`mpn`, `mfr`) REFERENCES `manufacturer_parts` (`mpn`, `mfr`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `supplier_part_numbers_FK_1` FOREIGN KEY (`supplier`) REFERENCES `suppliers` (`supplier`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `component_packages` (
  `component_package` varchar(50) NOT NULL,
  `package_display_name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`component_package`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `footprints_kicad` (
  `footprint_kicad_id` int(11) NOT NULL AUTO_INCREMENT,
  `component_package` varchar(50) DEFAULT NULL,
  `kicad_footprint` varchar(100) DEFAULT NULL,
  `kicad_footprint_filter` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`footprint_kicad_id`),
  UNIQUE KEY `footprints_kicad_AK` (`component_package`),
  CONSTRAINT `footprints_kicad_FK` FOREIGN KEY (`component_package`) REFERENCES `component_packages` (`component_package`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `component_symbols` (
  `component_symbol` varchar(50) NOT NULL,
  `symbol_display_name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`component_symbol`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `symbols_kicad` (
  `kicad_symbol_id` int(11) NOT NULL AUTO_INCREMENT,
  `component_symbol` varchar(50) DEFAULT NULL,
  `kicad_symbol` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`kicad_symbol_id`),
  UNIQUE KEY `symbols_kicad_AK` (`component_symbol`),
  KEY `symbols_kicad_FK` (`component_symbol`),
  CONSTRAINT `symbols_kicad_FK` FOREIGN KEY (`component_symbol`) REFERENCES `component_symbols` (`component_symbol`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


/* Utility function for converting values to electrical value strings, e.g. 4k7 */
DELIMITER //
CREATE OR REPLACE FUNCTION to_value_string(n float, unit varchar(1)) RETURNS varchar(10)
BEGIN
 DECLARE exponent int;
 DECLARE suffix varchar(2);
 DECLARE string_to_return varchar(10);

 IF n != 0 THEN
  SET exponent = floor(log10(n)/3);
 ELSE
  SET exponent = 0;
 END IF;

 SET exponent = greatest(-5, exponent);
 SET exponent = least(3, exponent);

 IF exponent <= -5 THEN
  SET suffix = 'f';
 ELSEIF exponent = -4 THEN
  SET suffix = 'p';
 ELSEIF exponent = -3 THEN
  SET suffix = 'n';
 ELSEIF exponent = -2 THEN
  SET suffix = 'u';
 ELSEIF exponent = -1 THEN
  SET suffix = 'm';
 ELSEIF exponent = 0 THEN
  SET suffix = unit;
 ELSEIF exponent = 1 THEN
  SET suffix = 'k';
 ELSEIF exponent = 2 THEN
  SET suffix = 'M';
 ELSEIF exponent >= 3 THEN
  SET suffix = 'G';
 END IF;

 SET string_to_return = trim( TRAILING '0' from CAST( round(n/(power(1000, exponent)), 3) AS varchar(10) ));
 -- SET string_to_return = n;

 IF instr(string_to_return, '.') THEN
  SET string_to_return = replace(string_to_return, '.', suffix);
 ELSE
  SET string_to_return = concat(string_to_return, suffix); 
 END IF;

 RETURN string_to_return;
END 
//
DELIMITER ;