JCL/sql/jegatroncomponents_tests.sql

/*
CALL `add_part_group`('krypton', 'Kryptoner', 'En krypton är typiskt dålig för stålmannen');
CALL `add_part_group`('visp', 'Vispar', 'Med en visp kan man göra smör');
CALL `add_part_group`('talisman', 'Tasmanska jävlar', 'Det är hittepå');
CALL `add_part_group`('Oloong', 'Semi-grönt/svart & Te', 'Mellantinget i fermenteringen av teet, brukar förekomma i rullade bollar. Bryggs i 85 grader, 4 minuter.');




CALL add_internal_part('Kinesiskt Oloong', 'Oloong', 'Kinesiskt Oloong som smakar mycket te', @fick_internal_pn); 
SELECT @fick_internal_pn;

SELECT * 
FROM internal_parts ip
JOIN part_groups pg ON ip.part_group_id = pg.part_group_id
JOIN group_details_oloong gdo ON ip.internal_pn = gdo.internal_pn 
WHERE ip.internal_pn = 4;


SET @part_display_name := 'Token item nr.1';
SET @part_group_id := 'krypton';
SET @part_description := 'This token will make thing great again';
CALL add_internal_part(@part_display_name, @part_group_id, @part_description, @mymandude);
SELECT @mymandude;

SET @part_display_name := 'Token item nr.2';
SET @part_group_id := 'krypton';
CALL add_internal_part(@part_display_name, @part_group_id, @part_description, @mymandude);
SELECT @mymandude;

SET @part_display_name := 'Token item nr.3';
SET @part_group_id := 'visp';
CALL add_internal_part(@part_display_name, @part_group_id, @part_description, @mymandude);
SELECT @mymandude;

DELETE FROM `internal_parts` WHERE `internal_pn` = 1;

SELECT * 
FROM `internal_parts` ip
JOIN `group_details_krypton` gdk ON ip.internal_pn = gdk.internal_pn; 
*/

CALL `add_part_group`('car', 'Cars', 'Automotive vehicles for transporting people');

ALTER TABLE jegatroncomponents.group_details_car ADD(
  `series` varchar(100) DEFAULT NULL,
  `color` varchar(50) NOT NULL,
  `release_date` timestamp NOT NULL
);

INSERT INTO manufacturers (mfr) VALUES ('volvo');


INSERT INTO manufacturer_parts (mpn, mfr, part_group_id, part_description, lifecycle_status)
VALUES ('S60', 'volvo', 'car', 'A good car', 'Obsolete'),
('S80', 'volvo', 'car', 'A bigger good car', 'Obsolete');


CALL `add_part_group`('chip_resistor', 'Chip Resistor - SMD', 'Surface mount resistors');

ALTER TABLE jegatroncomponents.group_details_chip_resistor ADD(
  `series` varchar(100) DEFAULT NULL,
  `resistance` float NOT NULL,
  `resistance_str` varchar(10) DEFAULT NULL,
  `resistance_tolerance_upper` float NOT NULL,
  `resistance_tolerance_lower` float NOT NULL,
  `power` float NOT NULL,
  `resistor_type` varchar(50) NOT NULL,
  `temperature_max` float NOT NULL,
  `temperature_min` float NOT NULL,
  `temperature_coefficient_upper` float NOT NULL,
  `temperature_coefficient_lower` float NOT NULL,
  `dimension_height` float NOT NULL,
  `dimension_width` float NOT NULL,
  `dimension_length` float NOT NULL,
  `component_package` varchar(50) NOT NULL,
  `component_symbol` varchar(50) NOT NULL,
  `number_of_terminals` int DEFAULT 2 NOT NULL,
  CONSTRAINT `group_details_chip_resistor_package_FK` FOREIGN KEY (`component_package`) REFERENCES `component_packages` (`component_package`) ON UPDATE CASCADE,
  CONSTRAINT `group_details_chip_resistor_symbol_FK` FOREIGN KEY (`component_symbol`) REFERENCES `component_symbols` (`component_symbol`) ON UPDATE CASCADE
);

delimiter //
CREATE OR REPLACE TRIGGER add_resistance_str
BEFORE INSERT ON jegatroncomponents.group_details_chip_resistor
FOR EACH ROW 
IF NEW.`resistance_str` IS NULL THEN
 SET NEW.resistance_str := to_value_string(NEW.resistance, 'R');
END IF;
//
CREATE OR REPLACE TRIGGER add_resistance_str
BEFORE UPDATE ON jegatroncomponents.group_details_chip_resistor
FOR EACH ROW 
IF NEW.`resistance_str` IS NULL THEN
 SET NEW.resistance_str := to_value_string(NEW.resistance, 'R');
END IF;
//
delimiter ;

DROP VIEW IF EXISTS kicad_resistors;
CREATE OR REPLACE VIEW kicad_resistors
AS
SELECT ip.internal_pn, to_pn_string(ip.internal_pn) AS internal_pn_string, ip.part_description AS description, gdcr.resistance AS value,
 cp.package_display_name, ip.exclude_from_bom, ip.exclude_from_board, fk.kicad_footprint,
 fk.kicad_footprint_filter, sk.kicad_symbol,
 gdcr.resistance_str AS value_str
FROM internal_parts ip
JOIN group_details_chip_resistor gdcr ON ip.internal_pn = gdcr.internal_pn
JOIN component_packages cp ON gdcr.component_package = cp.component_package
LEFT JOIN footprints_kicad fk ON gdcr.component_package = fk.component_package 
LEFT JOIN symbols_kicad sk ON gdcr.component_symbol = sk.component_symbol;

# SELECT * FROM kicad_resistors kr ;


INSERT INTO manufacturers (mfr) VALUES ('yageo');
INSERT INTO manufacturers (mfr) VALUES ('tdk');

DELETE FROM component_packages ;
INSERT INTO component_packages (component_package, package_display_name)
VALUES
('R01005', '01005 (0402 Metric)'),
('R0201', '0201 (0603 Metric)'),
('R0402', '0402 (1010 Metric)'),
('R0603', '0603 (1608 Metric)'),
('R0805', '0805 (2012 Metric)'),
('R1206', '1206 (3216 Metric)'),
('R1210', '1210 (3225 Metric)'),
('R1812', '1812 (4532 Metric)'),
('R2010', '2010 (5025 Metric)'),
('R2512', '2512 (6332 Metric)'),
('C01005', '01005 (0402 Metric)'),
('C0201', '0201 (0603 Metric)'),
('C0402', '0402 (1010 Metric)'),
('C0603', '0603 (1608 Metric)'),
('C0805', '0805 (2012 Metric)'),
('C1206', '1206 (3216 Metric)'),
('C1210', '1210 (3225 Metric)')
ON duplicate KEY UPDATE package_display_name = package_display_name;

DELETE FROM footprints_kicad ;
INSERT INTO footprints_kicad (component_package, kicad_footprint, kicad_footprint_filter)
VALUES
('R01005', 'Resistor_SMD:R_01005_0402Metric', 'Resistor_SMD:R_01005_*'),
('R0201', 'Resistor_SMD:R_0201_0603Metric', 'Resistor_SMD:R_0201_*'),
('R0402', 'Resistor_SMD:R_0402_1010Metric', 'Resistor_SMD:R_0402_*'),
('R0603', 'Resistor_SMD:R_0603_1608Metric', 'Resistor_SMD:R_0603_*'),
('R0805', 'Resistor_SMD:R_0805_2012Metric', 'Resistor_SMD:R_0805_*'),
('R1206', 'Resistor_SMD:R_1206_3216Metric', 'Resistor_SMD:R_1206_*'),
('R1210', 'Resistor_SMD:R_1210_3225Metric', 'Resistor_SMD:R_1210_*'),
('R1812', 'Resistor_SMD:R_1812_4532Metric', 'Resistor_SMD:R_1812_*'),
('R2010', 'Resistor_SMD:R_2010_5025Metric', 'Resistor_SMD:R_2010_*'),
('R2512', 'Resistor_SMD:R_2512_6332Metric', 'Resistor_SMD:R_2512_*'),
('C01005', 'Capacitor_SMD:R_01005_0402Metric', 'Capacitor_SMD:R_01005_*'),
('C0201', 'Capacitor_SMD:R_0201_0603Metric', 'Capacitor_SMD:R_0201_*'),
('C0402', 'Capacitor_SMD:R_0402_1010Metric', 'Capacitor_SMD:R_0402_*'),
('C0603', 'Capacitor_SMD:R_0603_1608Metric', 'Capacitor_SMD:R_0603_*'),
('C0805', 'Capacitor_SMD:R_0805_2012Metric', 'Capacitor_SMD:R_0805_*'),
('C1206', 'Capacitor_SMD:R_1206_3216Metric', 'Capacitor_SMD:R_1206_*'),
('C1210', 'Capacitor_SMD:R_1210_3225Metric', 'Capacitor_SMD:R_1210_*')
ON duplicate KEY UPDATE kicad_footprint = kicad_footprint;


INSERT INTO component_symbols (component_symbol, symbol_display_name)
VALUES
('resistor_2pin', 'Resistor'),
('capacitor_2pin', 'Capacitor');

INSERT INTO symbols_kicad (component_symbol, kicad_symbol)
VALUES
('resistor_2pin', 'Device:R'),
('capacitor_2pin', 'Device:C');