JCL/sql/Scratchpad.sql

describe resistors;

SET GLOBAL general_log=0;

select * from resistors ;

insert into resistors(`MPN`) VALUES('Yomandude');

show create table resistors;

drop table resistors ;

DELETE FROM internal_parts ;


SET @pn := CAST(678 AS int);
SET @pnstrtmp := lpad(cast(@pn AS char), 7, 0);
SELECT @pnstrtmp;
SET @pnstr := concat(substring(@pnstrtmp, 1, 2), '-', substring(@pnstrtmp, 3, 2), '-', substring(@pnstrtmp, 5, 3));
SELECT @pnstr;

CREATE TABLE `resistors` (
  `MPN` varchar(100) NOT NULL,
  `Symbols` varchar(100) DEFAULT NULL,
  `Footprints` varchar(100) DEFAULT NULL,
  `Value` varchar(20) DEFAULT NULL,
  `Description` varchar(100) DEFAULT NULL,
  `Footprint Filters` varchar(100) DEFAULT NULL,
  `Keywords` varchar(100) DEFAULT NULL,
  `No BOM` int DEFAULT 0,
  `Schematic Only` int DEFAULT 0,
  `Datasheet URL` varchar(150) default NULL,
  PRIMARY KEY `Resistors_un` (`MPN`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

alter table resistors modify `Schematic only`  int DEFAULT 0;

alter table resistors rename column `Schematic only` to `Exclude from board`;
alter table resistors rename column `No BOM` to `Exclude from BOM`;

CREATE TABLE `resistors` (
  `mpn` varchar(100) NOT NULL,
  `symbols` varchar(100) DEFAULT NULL,
  `footprints` varchar(100) DEFAULT NULL,
  `value` varchar(20) DEFAULT NULL,
  `description` varchar(100) DEFAULT NULL,
  `footprint_filters` varchar(100) DEFAULT NULL,
  `keywords` varchar(100) DEFAULT NULL,
  `exclude_from_bom` int(11) DEFAULT 0,
  `exclude_from_board` int(11) DEFAULT 0,
  `datasheet_url` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`MPN`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci


show create table internal_parts ;

explain select * from manufacturer_parts mp left join supplier_part_numbers spn on mp.mpn = spn.mpn and mp.mfr = spn.mfr ;

select * from internal_parts ip where part_type = 'Chip Resistor';

show create table manufacturer_parts ;

CREATE TABLE `manufacturer_parts` (
  `mpn` varchar(50) NOT NULL,
  `mfr` varchar(50) NOT NULL,
  `internal_pno` int(11) DEFAULT NULL,
  PRIMARY KEY (`mpn`,`mfr`),
  KEY `manufacturer_parts_FK_1` (`internal_pno`),
  KEY `manufacturer_parts_FK` (`mfr`),
  CONSTRAINT `manufacturer_parts_FK` FOREIGN KEY (`mfr`) REFERENCES `manufacturers` (`mfr`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

select * from internal_parts ip
join internal_resistor_parameters irp ON ip.internal_pno = irp.internal_pno
join resistor_parameters rp on irp.resistor_parameters_id = rp.id ;


select * from manufacturer_parts mp 
join manufacturer_resistor_parameters mrp ON mp.mpn = mrp.mpn and mp.mfr = mrp.mfr
join resistor_parameters rp on mrp.resistor_parameters_id = rp.id ;

select * from resistor_parameters rp ;

# CONSTRAINT CHK_NULL CHECK ((SOLD IS NOT NULL) OR (MANUFACTURED IS NOT NULL) OR (PURCHASED IS NOT NULL))

ALTER TABLE resistor_parameters ADD CONSTRAINT resistor_parameters_check check (mfr is not null);

alter table resistor_parameters drop constraint resistor_parameters_check;

ALTER TABLE resistor_parameters ADD CONSTRAINT resistor_parameters_check_owner check
(((internal_pno is not null) and ((mpn is null) and (mfr is null))) or
((internal_pno is null) and ((mpn is not null) and (mfr is not null))));


CREATE TABLE `A` (
 `id` int NOT NULL AUTO_INCREMENT,
 `value` int,
 PRIMARY KEY (`id`)
);
drop table if exists A;


INSERT INTO `A` (`value`) VALUES (5);
SELECT LAST_INSERT_ID();

CREATE TABLE `B` (
 `id` int NOT NULL,
 `val1` int,
 PRIMARY KEY (`id`),
 CONSTRAINT `a_nonsense_foreign_key` FOREIGN KEY (`val1`) REFERENCES `A` (`id`) ON UPDATE CASCADE,
 CONSTRAINT `a_nonsense_check` CHECK (`val1` > 10)
);
drop table if exists B;

CONSTRAINT `only_one_value` CHECK ((`val1`IS NOT NULL AND `val2` IS NULL) OR (`val1` IS NULL AND `val2` IS NOT NULL))

show create table resistor_parameters;
CREATE TABLE `resistor_parameters` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `internal_pno` int(11) DEFAULT NULL,
  `mpn` varchar(50) DEFAULT NULL,
  `mfr` varchar(50) DEFAULT NULL,
  `val2` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `resistor_parameters_un` (`internal_pno`),
  UNIQUE KEY `resistor_parameters_un_2` (`mpn`,`mfr`),
  CONSTRAINT `resistor_parameters_check_owner` CHECK (`internal_pno` is not null and `mpn` is null and `mfr` is null or `internal_pno` is null and `mpn` is not null and `mfr` is not null)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci


show create table internal_parts;
CREATE TABLE `internal_parts` (
  `internal_pno` int(11) NOT NULL AUTO_INCREMENT,
  `part_name` varchar(50) NOT NULL,
  `description` varchar(100) DEFAULT NULL,
  `part_type` varchar(50) NOT NULL,
  PRIMARY KEY (`internal_pno`),
  UNIQUE KEY `internal_parts_un` (`part_name`),
  KEY `internal_parts_FK` (`part_type`),
  CONSTRAINT `internal_parts_FK` FOREIGN KEY (`part_type`) REFERENCES `part_types` (`part_type`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci


SELECT concat ('yoman', ' dude', ' shit', 4) AS `a`;

PREPARE test_statement FROM concat('select * from ', 'a', ';');
EXECUTE test_statement;
DEALLOCATE PREPARE test_statement;


USe node_todo_list;

CREATE TABLE todos (
	id_code varchar(36) NOT NULL UNIQUE,
	to_do varchar(255) NOT NULL,
	completed boolean
);

CREATE USER 'todo_admin'@'localhost' IDENTIFIED BY 'leelu_dallas_multipass-6';
GRANT SELECT, INSERT, UPDATE ON node_todo_list.* TO 'todo_admin'@'localhost';
INSERT INTO todos (id_code, to_do, completed) VALUES ('todo1','Get something done', TRUE);

INSERT INTO todos (id_code, to_do, completed) VALUES ('todo2','Get another thing done', FALSE);

SHOW CREATE TABLE qualified_parts ;

SELECT ip.internal_pn, 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  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;

CREATE OR REPLACE VIEW kicad_resistors
AS
SELECT ip.internal_pn, 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  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;


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);
 
 SET exponent = floor(log10(n)/3);
 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 ;

SELECT to_value_string(0.001, 'R');
SELECT to_value_string(1, 'R');
SELECT to_value_string(1.001, 'R');
SELECT to_value_string(10100, 'R');
SELECT to_value_string(333333, 'R');