data_definition

CREATE TABLE tab_dt (
  id VARCHAR(255) NOT NULL,
  rid VARCHAR(255) NOT NULL,
  bid BIGINT DEFAULT NULL,
  name VARCHAR(255) NOT NULL,
  description LONGTEXT NULL,
  ldtn VARCHAR(255) DEFAULT NULL,
  ldate DATETIME DEFAULT NULL,
  lvers int NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY tab_dt_rid (rid),
  KEY tab_dt_name (name),
  KEY tab_dt_bid (bid)
) ENGINE = InnoDB;

--

CREATE TABLE tab_fm (
  id BIGINT NOT NULL DEFAULT 0,
  mad BIGINT NOT NULL DEFAULT 0,
  name VARCHAR(255) NOT NULL,
  lar SMALLINT NOT NULL DEFAULT 0,
  lun SMALLINT NOT NULL DEFAULT 0,
  fex LONGTEXT NULL,
  ltyp SMALLINT NOT NULL DEFAULT 0,
  ntyp VARCHAR(255) DEFAULT NULL,
  rtyp VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY tab_fm_idx_1 (name),
  KEY tab_fm_idx_2 (mad)
) ENGINE = InnoDB;

--

CREATE TABLE tab_str (
  bid BIGINT NOT NULL DEFAULT 0,
  mad BIGINT NOT NULL DEFAULT 0,
  sval VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (bid, mad),
  KEY tab_str_idx_1 (mad),
  KEY tab_str_idx_2 (sval)
) ENGINE = InnoDB;

--

CREATE TABLE tab_inf (
  id BIGINT NOT NULL DEFAULT 0,
  rid VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
) ENGINE = InnoDB;

--

CREATE OR REPLACE VIEW vw_sf AS
SELECT
    tab_str.bid,
    tab_str.mad,
    tab_str.sval,
    tab_fm.name,
    tab_fm.lar,
    tab_fm.ltyp,
    tab_fm.ntyp,
    tab_fm.rtyp
FROM
    tab_str
    LEFT JOIN tab_fm ON tab_str.mad = tab_fm.id;

--

INSERT INTO tab_inf(id,rid) VALUES(1,'AA');
INSERT INTO tab_inf(id,rid) VALUES(2,'AA');
INSERT INTO tab_inf(id,rid) VALUES(3,'AA');
INSERT INTO tab_inf(id,rid) VALUES(4,'AA');
INSERT INTO tab_inf(id,rid) VALUES(5,'AA');
INSERT INTO tab_inf(id,rid) VALUES(6,'BB');

--

INSERT INTO tab_dt(id,rid,bid,name,description,ldtn,ldate,lvers) VALUES ('id1','AA',1,'name1',NULL,'19-1',now(),1);
INSERT INTO tab_dt(id,rid,bid,name,description,ldtn,ldate,lvers) VALUES ('id2','AA',2,'name2',NULL,'19-2',now(),1);
INSERT INTO tab_dt(id,rid,bid,name,description,ldtn,ldate,lvers) VALUES ('id3','AA',3,'name2',NULL,'19-3',now(),1);
INSERT INTO tab_dt(id,rid,bid,name,description,ldtn,ldate,lvers) VALUES ('id4','AA',4,'name2',NULL,'19-4',now(),1);
INSERT INTO tab_dt(id,rid,bid,name,description,ldtn,ldate,lvers) VALUES ('id5','AA',5,'name2',NULL,'19-5',now(),1);

--

INSERT INTO tab_fm(id,name,ltyp) VALUES(101,'field1',33);
INSERT INTO tab_fm(id,name,ltyp) VALUES(102,'field2',33);
INSERT INTO tab_fm(id,name,ltyp) VALUES(103,'field3',33);
INSERT INTO tab_fm(id,name,ltyp) VALUES(104,'field4',33);
INSERT INTO tab_fm(id,name,ltyp) VALUES(105,'field5',33);
INSERT INTO tab_fm(id,name,ltyp) VALUES(106,'field6',33);
INSERT INTO tab_fm(id,name,ltyp) VALUES(107,'field7',33);
INSERT INTO tab_fm(id,name,ltyp) VALUES(108,'field8',33);
INSERT INTO tab_fm(id,name,ltyp) VALUES(109,'field9',33);
INSERT INTO tab_fm(id,name,ltyp) VALUES(110,'field10',33);
INSERT INTO tab_fm(id,name,ltyp) VALUES(111,'field11',33);
INSERT INTO tab_fm(id,name,ltyp) VALUES(112,'field12',33);
INSERT INTO tab_fm(id,name,ltyp) VALUES(113,'field13',33);
INSERT INTO tab_fm(id,name,ltyp) VALUES(114,'field14',33);
INSERT INTO tab_fm(id,name,ltyp) VALUES(115,'field15',33);
INSERT INTO tab_fm(id,name,ltyp) VALUES(116,'field16',33);
INSERT INTO tab_fm(id,name,ltyp) VALUES(117,'field17',33);
INSERT INTO tab_fm(id,name,ltyp) VALUES(118,'field18',33);
INSERT INTO tab_fm(id,name,ltyp) VALUES(119,'field19',33);
INSERT INTO tab_fm(id,name,ltyp) VALUES(120,'field20',33);
INSERT INTO tab_fm(id,name,ltyp) VALUES(121,'field21',33);
INSERT INTO tab_fm(id,name,ltyp) VALUES(122,'field22',33);
INSERT INTO tab_fm(id,name,ltyp) VALUES(123,'field23',33);
INSERT INTO tab_fm(id,name,ltyp) VALUES(124,'field24',33);
INSERT INTO tab_fm(id,name,ltyp) VALUES(125,'field25',33);
INSERT INTO tab_fm(id,name,ltyp) VALUES(126,'field26',33);
INSERT INTO tab_fm(id,name,ltyp) VALUES(127,'field27',33);
INSERT INTO tab_fm(id,name,ltyp) VALUES(128,'field28',33);
INSERT INTO tab_fm(id,name,ltyp) VALUES(129,'field29',33);
INSERT INTO tab_fm(id,name,ltyp) VALUES(130,'field30',33);

--

INSERT INTO tab_str(bid,mad,sval) VALUES(1,101,'str-1-1');
INSERT INTO tab_str(bid,mad,sval) VALUES(1,102,'str-1-2');
INSERT INTO tab_str(bid,mad,sval) VALUES(1,103,'str-1-3');
INSERT INTO tab_str(bid,mad,sval) VALUES(1,104,'str-1-4');
INSERT INTO tab_str(bid,mad,sval) VALUES(1,105,'str-1-5');
INSERT INTO tab_str(bid,mad,sval) VALUES(1,106,'str-1-6');
INSERT INTO tab_str(bid,mad,sval) VALUES(1,107,'str-1-7');
INSERT INTO tab_str(bid,mad,sval) VALUES(1,108,'str-1-8');
INSERT INTO tab_str(bid,mad,sval) VALUES(1,109,'str-1-9');
INSERT INTO tab_str(bid,mad,sval) VALUES(1,110,'str-1-10');
INSERT INTO tab_str(bid,mad,sval) VALUES(1,111,'str-1-11');
INSERT INTO tab_str(bid,mad,sval) VALUES(1,112,'str-1-12');
INSERT INTO tab_str(bid,mad,sval) VALUES(1,113,'str-1-13');
INSERT INTO tab_str(bid,mad,sval) VALUES(1,114,'str-1-14');
INSERT INTO tab_str(bid,mad,sval) VALUES(1,115,'str-1-15');
INSERT INTO tab_str(bid,mad,sval) VALUES(1,116,'str-1-16');
INSERT INTO tab_str(bid,mad,sval) VALUES(1,117,'str-1-17');
INSERT INTO tab_str(bid,mad,sval) VALUES(1,118,'str-1-18');
INSERT INTO tab_str(bid,mad,sval) VALUES(1,119,'str-1-19');
INSERT INTO tab_str(bid,mad,sval) VALUES(1,120,'str-1-20');
INSERT INTO tab_str(bid,mad,sval) VALUES(1,121,'str-1-21');
INSERT INTO tab_str(bid,mad,sval) VALUES(1,122,'str-1-22');
INSERT INTO tab_str(bid,mad,sval) VALUES(1,123,'str-1-23');
INSERT INTO tab_str(bid,mad,sval) VALUES(1,124,'str-1-24');
INSERT INTO tab_str(bid,mad,sval) VALUES(1,125,'str-1-25');
INSERT INTO tab_str(bid,mad,sval) VALUES(1,126,'str-1-26');
INSERT INTO tab_str(bid,mad,sval) VALUES(1,127,'str-1-27');
INSERT INTO tab_str(bid,mad,sval) VALUES(1,128,'str-1-28');
INSERT INTO tab_str(bid,mad,sval) VALUES(1,129,'str-1-29');
INSERT INTO tab_str(bid,mad,sval) VALUES(1,130,'str-1-30');

INSERT INTO tab_str(bid,mad,sval) VALUES(2,101,'str-2-1');
INSERT INTO tab_str(bid,mad,sval) VALUES(2,102,'str-2-2');
INSERT INTO tab_str(bid,mad,sval) VALUES(2,103,'str-2-3');
INSERT INTO tab_str(bid,mad,sval) VALUES(2,104,'str-2-4');
INSERT INTO tab_str(bid,mad,sval) VALUES(2,105,'str-2-5');
INSERT INTO tab_str(bid,mad,sval) VALUES(2,106,'str-2-6');
INSERT INTO tab_str(bid,mad,sval) VALUES(2,107,'str-2-7');
INSERT INTO tab_str(bid,mad,sval) VALUES(2,108,'str-2-8');

-- etc