SHARE
TWEET

data_definition

a guest May 27th, 2019 171 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TABLE tab_dt (
  2.   id VARCHAR(255) NOT NULL,
  3.   rid VARCHAR(255) NOT NULL,
  4.   bid BIGINT DEFAULT NULL,
  5.   name VARCHAR(255) NOT NULL,
  6.   description LONGTEXT NULL,
  7.   ldtn VARCHAR(255) DEFAULT NULL,
  8.   ldate DATETIME DEFAULT NULL,
  9.   lvers int NOT NULL DEFAULT 0,
  10.   PRIMARY KEY (id),
  11.   KEY tab_dt_rid (rid),
  12.   KEY tab_dt_name (name),
  13.   KEY tab_dt_bid (bid)
  14. ) ENGINE = InnoDB;
  15.  
  16. --
  17.  
  18. CREATE TABLE tab_fm (
  19.   id BIGINT NOT NULL DEFAULT 0,
  20.   mad BIGINT NOT NULL DEFAULT 0,
  21.   name VARCHAR(255) NOT NULL,
  22.   lar SMALLINT NOT NULL DEFAULT 0,
  23.   lun SMALLINT NOT NULL DEFAULT 0,
  24.   fex LONGTEXT NULL,
  25.   ltyp SMALLINT NOT NULL DEFAULT 0,
  26.   ntyp VARCHAR(255) DEFAULT NULL,
  27.   rtyp VARCHAR(255) DEFAULT NULL,
  28.   PRIMARY KEY (id),
  29.   KEY tab_fm_idx_1 (name),
  30.   KEY tab_fm_idx_2 (mad)
  31. ) ENGINE = InnoDB;
  32.  
  33. --
  34.  
  35. CREATE TABLE tab_str (
  36.   bid BIGINT NOT NULL DEFAULT 0,
  37.   mad BIGINT NOT NULL DEFAULT 0,
  38.   sval VARCHAR(255) DEFAULT NULL,
  39.   PRIMARY KEY (bid, mad),
  40.   KEY tab_str_idx_1 (mad),
  41.   KEY tab_str_idx_2 (sval)
  42. ) ENGINE = InnoDB;
  43.  
  44. --
  45.  
  46. CREATE TABLE tab_inf (
  47.   id BIGINT NOT NULL DEFAULT 0,
  48.   rid VARCHAR(255) NOT NULL,
  49.   PRIMARY KEY (id)
  50. ) ENGINE = InnoDB;
  51.  
  52. --
  53.  
  54. CREATE OR REPLACE VIEW vw_sf AS
  55. SELECT    
  56.     tab_str.bid,
  57.     tab_str.mad,
  58.     tab_str.sval,
  59.     tab_fm.name,
  60.     tab_fm.lar,
  61.     tab_fm.ltyp,
  62.     tab_fm.ntyp,
  63.     tab_fm.rtyp
  64. FROM
  65.     tab_str
  66.     LEFT JOIN tab_fm ON tab_str.mad = tab_fm.id;  
  67.  
  68. --
  69.  
  70. INSERT INTO tab_inf(id,rid) VALUES(1,'AA');
  71. INSERT INTO tab_inf(id,rid) VALUES(2,'AA');
  72. INSERT INTO tab_inf(id,rid) VALUES(3,'AA');
  73. INSERT INTO tab_inf(id,rid) VALUES(4,'AA');
  74. INSERT INTO tab_inf(id,rid) VALUES(5,'AA');
  75. INSERT INTO tab_inf(id,rid) VALUES(6,'BB');
  76.  
  77. --
  78.  
  79. INSERT INTO tab_dt(id,rid,bid,name,description,ldtn,ldate,lvers) VALUES ('id1','AA',1,'name1',NULL,'19-1',now(),1);
  80. INSERT INTO tab_dt(id,rid,bid,name,description,ldtn,ldate,lvers) VALUES ('id2','AA',2,'name2',NULL,'19-2',now(),1);
  81. INSERT INTO tab_dt(id,rid,bid,name,description,ldtn,ldate,lvers) VALUES ('id3','AA',3,'name2',NULL,'19-3',now(),1);
  82. INSERT INTO tab_dt(id,rid,bid,name,description,ldtn,ldate,lvers) VALUES ('id4','AA',4,'name2',NULL,'19-4',now(),1);
  83. INSERT INTO tab_dt(id,rid,bid,name,description,ldtn,ldate,lvers) VALUES ('id5','AA',5,'name2',NULL,'19-5',now(),1);
  84.  
  85. --
  86.  
  87. INSERT INTO tab_fm(id,name,ltyp) VALUES(101,'field1',33);
  88. INSERT INTO tab_fm(id,name,ltyp) VALUES(102,'field2',33);
  89. INSERT INTO tab_fm(id,name,ltyp) VALUES(103,'field3',33);
  90. INSERT INTO tab_fm(id,name,ltyp) VALUES(104,'field4',33);
  91. INSERT INTO tab_fm(id,name,ltyp) VALUES(105,'field5',33);
  92. INSERT INTO tab_fm(id,name,ltyp) VALUES(106,'field6',33);
  93. INSERT INTO tab_fm(id,name,ltyp) VALUES(107,'field7',33);
  94. INSERT INTO tab_fm(id,name,ltyp) VALUES(108,'field8',33);
  95. INSERT INTO tab_fm(id,name,ltyp) VALUES(109,'field9',33);
  96. INSERT INTO tab_fm(id,name,ltyp) VALUES(110,'field10',33);
  97. INSERT INTO tab_fm(id,name,ltyp) VALUES(111,'field11',33);
  98. INSERT INTO tab_fm(id,name,ltyp) VALUES(112,'field12',33);
  99. INSERT INTO tab_fm(id,name,ltyp) VALUES(113,'field13',33);
  100. INSERT INTO tab_fm(id,name,ltyp) VALUES(114,'field14',33);
  101. INSERT INTO tab_fm(id,name,ltyp) VALUES(115,'field15',33);
  102. INSERT INTO tab_fm(id,name,ltyp) VALUES(116,'field16',33);
  103. INSERT INTO tab_fm(id,name,ltyp) VALUES(117,'field17',33);
  104. INSERT INTO tab_fm(id,name,ltyp) VALUES(118,'field18',33);
  105. INSERT INTO tab_fm(id,name,ltyp) VALUES(119,'field19',33);
  106. INSERT INTO tab_fm(id,name,ltyp) VALUES(120,'field20',33);
  107. INSERT INTO tab_fm(id,name,ltyp) VALUES(121,'field21',33);
  108. INSERT INTO tab_fm(id,name,ltyp) VALUES(122,'field22',33);
  109. INSERT INTO tab_fm(id,name,ltyp) VALUES(123,'field23',33);
  110. INSERT INTO tab_fm(id,name,ltyp) VALUES(124,'field24',33);
  111. INSERT INTO tab_fm(id,name,ltyp) VALUES(125,'field25',33);
  112. INSERT INTO tab_fm(id,name,ltyp) VALUES(126,'field26',33);
  113. INSERT INTO tab_fm(id,name,ltyp) VALUES(127,'field27',33);
  114. INSERT INTO tab_fm(id,name,ltyp) VALUES(128,'field28',33);
  115. INSERT INTO tab_fm(id,name,ltyp) VALUES(129,'field29',33);
  116. INSERT INTO tab_fm(id,name,ltyp) VALUES(130,'field30',33);
  117.  
  118. --
  119.  
  120. INSERT INTO tab_str(bid,mad,sval) VALUES(1,101,'str-1-1');
  121. INSERT INTO tab_str(bid,mad,sval) VALUES(1,102,'str-1-2');
  122. INSERT INTO tab_str(bid,mad,sval) VALUES(1,103,'str-1-3');
  123. INSERT INTO tab_str(bid,mad,sval) VALUES(1,104,'str-1-4');
  124. INSERT INTO tab_str(bid,mad,sval) VALUES(1,105,'str-1-5');
  125. INSERT INTO tab_str(bid,mad,sval) VALUES(1,106,'str-1-6');
  126. INSERT INTO tab_str(bid,mad,sval) VALUES(1,107,'str-1-7');
  127. INSERT INTO tab_str(bid,mad,sval) VALUES(1,108,'str-1-8');
  128. INSERT INTO tab_str(bid,mad,sval) VALUES(1,109,'str-1-9');
  129. INSERT INTO tab_str(bid,mad,sval) VALUES(1,110,'str-1-10');
  130. INSERT INTO tab_str(bid,mad,sval) VALUES(1,111,'str-1-11');
  131. INSERT INTO tab_str(bid,mad,sval) VALUES(1,112,'str-1-12');
  132. INSERT INTO tab_str(bid,mad,sval) VALUES(1,113,'str-1-13');
  133. INSERT INTO tab_str(bid,mad,sval) VALUES(1,114,'str-1-14');
  134. INSERT INTO tab_str(bid,mad,sval) VALUES(1,115,'str-1-15');
  135. INSERT INTO tab_str(bid,mad,sval) VALUES(1,116,'str-1-16');
  136. INSERT INTO tab_str(bid,mad,sval) VALUES(1,117,'str-1-17');
  137. INSERT INTO tab_str(bid,mad,sval) VALUES(1,118,'str-1-18');
  138. INSERT INTO tab_str(bid,mad,sval) VALUES(1,119,'str-1-19');
  139. INSERT INTO tab_str(bid,mad,sval) VALUES(1,120,'str-1-20');
  140. INSERT INTO tab_str(bid,mad,sval) VALUES(1,121,'str-1-21');
  141. INSERT INTO tab_str(bid,mad,sval) VALUES(1,122,'str-1-22');
  142. INSERT INTO tab_str(bid,mad,sval) VALUES(1,123,'str-1-23');
  143. INSERT INTO tab_str(bid,mad,sval) VALUES(1,124,'str-1-24');
  144. INSERT INTO tab_str(bid,mad,sval) VALUES(1,125,'str-1-25');
  145. INSERT INTO tab_str(bid,mad,sval) VALUES(1,126,'str-1-26');
  146. INSERT INTO tab_str(bid,mad,sval) VALUES(1,127,'str-1-27');
  147. INSERT INTO tab_str(bid,mad,sval) VALUES(1,128,'str-1-28');
  148. INSERT INTO tab_str(bid,mad,sval) VALUES(1,129,'str-1-29');
  149. INSERT INTO tab_str(bid,mad,sval) VALUES(1,130,'str-1-30');
  150.  
  151. INSERT INTO tab_str(bid,mad,sval) VALUES(2,101,'str-2-1');
  152. INSERT INTO tab_str(bid,mad,sval) VALUES(2,102,'str-2-2');
  153. INSERT INTO tab_str(bid,mad,sval) VALUES(2,103,'str-2-3');
  154. INSERT INTO tab_str(bid,mad,sval) VALUES(2,104,'str-2-4');
  155. INSERT INTO tab_str(bid,mad,sval) VALUES(2,105,'str-2-5');
  156. INSERT INTO tab_str(bid,mad,sval) VALUES(2,106,'str-2-6');
  157. INSERT INTO tab_str(bid,mad,sval) VALUES(2,107,'str-2-7');
  158. INSERT INTO tab_str(bid,mad,sval) VALUES(2,108,'str-2-8');
  159.  
  160. -- etc
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top