Working with Metadata

Sechema Generation Scripts

• example
  • 1

    SELECT 'CREATE TABLE category (' create_table_statement
    UNION ALL
    SELECT cols.txt
    FROM
    (SELECT CONCAT(' ',column_name, ' ', column_type,
      CASE
          WHEN is_nullable = 'NO' THEN ' not null'
      ELSE ''
      END,
      CASE
          WHEN extra IS NOT NULL AND extra LIKE 'DEFAULT_GENERATED%'
      THEN concat((' DEFAULT ' ), column_default, substr(extra,18))
      WHEN extra IS NOT NULL THEN concat(' ', extra)
    ELSE ''
    END, ',') txt
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = 'sakila' AND TABLE_NAME = 'category'
    ORDER BY ORDINAL_POSITION) cols
    UNION ALL
    SELECT ')';
    

  • 2

    SELECT 'CREATE TABLE category (' create_table_statement
    UNION ALL
    SELECT cols.txt
    FROM
    (SELECT CONCAT(' ',column_name, ' ', column_type,
      CASE
          WHEN is_nullable = 'NO' THEN ' not null'
      ELSE ''
      END,
      CASE
          WHEN extra IS NOT NULL AND extra LIKE 'DEFAULT_GENERATED%'
      THEN concat((' DEFAULT ' ), column_default, substr(extra,18))
      WHEN extra IS NOT NULL THEN concat(' ', extra)
    ELSE ''
    END, ',') txt
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = 'sakila' AND TABLE_NAME = 'category'
    ORDER BY ORDINAL_POSITION) cols
    UNION ALL
    SELECT concat(' constraint primary key(')
    UNION ALL
    SELECT cols.txt
    FROM
    (SELECT concat(CASE WHEN ORDINAL_POSITION > 1 THEN ' ,' ELSE ' ' END,COLUMN_NAME) txt
      FROM information_schema.KEY_COLUMN_USAGE
      WHERE TABLE_SCHEMA = 'sakila' AND TABLE_NAME = 'category'
      AND CONSTRAINT_NAME = 'PRIMARY'
      ORDER BY ORDINAL_POSITION) cols
    UNION ALL
    SELECT ' )'
    UNION ALL
    SELECT ')';
    

Deployment Verification

• example

  SELECT tbl.table_name, 
  (SELECT COUNT(*)
      FROM information_schema.COLUMNS clm
      WHERE clm.TABLE_SCHEMA = tbl.table_schema
      AND clm.TABLE_NAME = tbl.TABLE_NAME) num_columns,
  (SELECT COUNT(*)
      FROM information_schema.STATISTICS sta
      WHERE sta.TABLE_SCHEMA = tbl.TABLE_SCHEMA
      AND sta.TABLE_NAME = tbl.TABLE_NAME) num_indexs,
  (SELECT COUNT(*)
      FROM information_schema.TABLE_CONSTRAINTS tc
      WHERE tc.TABLE_SCHEMA = tbl.TABLE_SCHEMA
      AND tc.TABLE_NAME = tbl.TABLE_NAME
      AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY') num_primary_keys
  FROM information_schema.TABLES tbl
  WHERE tbl.TABLE_SCHEMA = 'sakila' AND tbl.TABLE_TYPE = 'BASE TABLE'
  ORDER BY 1;
  

Dynamic SQL Generation

• 将SQL statement写为string，然后执行SQL statement
• Statement
  • EXECUTE
  • PREPARE
  • DEALLOCATE
• demo

  SET @${variable_name} = ${SQL statements};
  PREPARE ${variable_name2} FROM @${variable_name};
  EXECUTE ${variable_name2};
  DEALLOCATE PREPARE ￥{variable_name2};
  

• placeholder

  • ? USING

  • example

    SET @qry = 'SELECT customer_id, first_name, last_name
        FROM customer WHERE customer_id = ?';
    
    PREPARE dynsql2 FROM @qry;
    SET @custid = 9;
    EXECUTE dynsql2 USING @custid;
    SET @custid = 12;
    EXECUTE dynsql2 USING @custid;
    

• 和Metadata结合

  # 将得到的String写入@qry
  SELECT CONCAT('SELECT ', concat_ws(',',cols.col1, cols.col2, cols.col3, cols.col4,
      cols.col5, cols.col6, cols.col7, cols.col8, cols.col9),' FROM customer WHERE customer_id = ?')
  INTO @qry
  FROM
  (SELECT
      max(CASE WHEN ORDINAL_POSITION = 1 THEN COLUMN_NAME ELSE NULL END) col1,
      max(CASE WHEN ORDINAL_POSITION = 2 THEN COLUMN_NAME ELSE NULL END) col2,
      max(CASE WHEN ORDINAL_POSITION = 3 THEN COLUMN_NAME ELSE NULL END) col3,
      max(CASE WHEN ORDINAL_POSITION = 4 THEN COLUMN_NAME ELSE NULL END) col4,
      max(CASE WHEN ORDINAL_POSITION = 5 THEN COLUMN_NAME ELSE NULL END) col5,
      max(CASE WHEN ORDINAL_POSITION = 6 THEN COLUMN_NAME ELSE NULL END) col6,
      max(CASE WHEN ORDINAL_POSITION = 7 THEN COLUMN_NAME ELSE NULL END) col7,
      max(CASE WHEN ORDINAL_POSITION = 8 THEN COLUMN_NAME ELSE NULL END) col8,
      max(CASE WHEN ORDINAL_POSITION = 9 THEN COLUMN_NAME ELSE NULL END) col9
      FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = 'sakila' AND TABLE_NAME = 'customer'
      GROUP BY TABLE_NAME) cols;
  
  # prepare @qry
  PREPARE dynsql3 FROM @qry;
  
  # execute dynsql3
  SET @custid = 45;
  EXECUTE dynsql3 USING @custid;
  DEALLOCATE dynsql3;