SQLRETURN foreign_keys_i_s()

in driver/catalog.cc [1583:1734]

• 128 lines of code
• 11 McCabe index (conditional complexity)

SQLRETURN foreign_keys_i_s(SQLHSTMT hstmt,
                           SQLCHAR    *pk_catalog,
                           SQLSMALLINT pk_catalog_len,
                           SQLCHAR    *pk_schema,
                           SQLSMALLINT pk_schema_len,
                           SQLCHAR    *pk_table,
                           SQLSMALLINT pk_table_len,
                           SQLCHAR    *fk_catalog,
                           SQLSMALLINT fk_catalog_len,
                           SQLCHAR    *fk_schema,
                           SQLSMALLINT fk_schema_len,
                           SQLCHAR    *fk_table,
                           SQLSMALLINT fk_table_len)
{
  STMT *stmt=(STMT *) hstmt;
  CONNECTION_PROXY *connection_proxy= stmt->dbc->connection_proxy;
  char tmpbuff[1024]; /* This should be big enough. */
  const char *update_rule, *delete_rule, *ref_constraints_join;
  SQLRETURN rc;
  std::string query, pk_db, fk_db, order_by;
  query.reserve(4096);
  size_t cnt = 0;

  pk_db = get_database_name(stmt, pk_catalog, pk_catalog_len,
                            pk_schema, pk_schema_len, false);
  fk_db = get_database_name(stmt, fk_catalog, fk_catalog_len,
                            fk_schema, fk_schema_len, false);

  /*
     With 5.1, we can use REFERENTIAL_CONSTRAINTS to get even more info.
  */
  if (is_minimum_version(stmt->dbc->connection_proxy->get_server_version(), "5.1"))
  {
    update_rule= "CASE"
                 " WHEN R.UPDATE_RULE = 'CASCADE' THEN 0"
                 " WHEN R.UPDATE_RULE = 'SET NULL' THEN 2"
                 " WHEN R.UPDATE_RULE = 'SET DEFAULT' THEN 4"
                 " WHEN R.UPDATE_RULE = 'SET RESTRICT' THEN 1"
                 " WHEN R.UPDATE_RULE = 'SET NO ACTION' THEN 3"
                 " ELSE 3"
                 " END";
    delete_rule= "CASE"
                 " WHEN R.DELETE_RULE = 'CASCADE' THEN 0"
                 " WHEN R.DELETE_RULE = 'SET NULL' THEN 2"
                 " WHEN R.DELETE_RULE = 'SET DEFAULT' THEN 4"
                 " WHEN R.DELETE_RULE = 'SET RESTRICT' THEN 1"
                 " WHEN R.DELETE_RULE = 'SET NO ACTION' THEN 3"
                 " ELSE 3"
                 " END";

    ref_constraints_join=
      " JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R"
      " ON (R.CONSTRAINT_NAME = A.CONSTRAINT_NAME"
      " AND R.TABLE_NAME = A.TABLE_NAME"
      " AND R.CONSTRAINT_SCHEMA = A.TABLE_SCHEMA)";
  }
  else
  {
    /* Just return '1' to be compatible with pre-I_S version. */
    update_rule= delete_rule= "1";
    ref_constraints_join= "";
  }

  /* This is a big, ugly query. But it works! */
  if(!pk_schema_len)
    query = "SELECT A.REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT,"
            "NULL AS PKTABLE_SCHEM,";
  else
    query = "SELECT NULL AS PKTABLE_CAT,"
            "A.REFERENCED_TABLE_SCHEMA AS PKTABLE_SCHEM,";

  query.append("A.REFERENCED_TABLE_NAME AS PKTABLE_NAME,"
          "A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME,");

  if(!pk_schema_len)
    query.append("A.TABLE_SCHEMA AS FKTABLE_CAT, NULL AS FKTABLE_SCHEM,");
  else
    query.append("NULL AS FKTABLE_CAT, A.TABLE_SCHEMA AS FKTABLE_SCHEM,");

  query.append("A.TABLE_NAME AS FKTABLE_NAME,"
          "A.COLUMN_NAME AS FKCOLUMN_NAME,"
          "A.ORDINAL_POSITION AS KEY_SEQ,");
  query.append(update_rule).append(" AS UPDATE_RULE,").append(delete_rule);
  query.append(" AS DELETE_RULE,"
                "A.CONSTRAINT_NAME AS FK_NAME,"
                "'PRIMARY' AS PK_NAME,"
                "7 AS DEFERRABILITY"
                " FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A"
                " JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE D"
                " ON (D.TABLE_SCHEMA=A.REFERENCED_TABLE_SCHEMA AND D.TABLE_NAME=A.REFERENCED_TABLE_NAME"
                " AND D.COLUMN_NAME=A.REFERENCED_COLUMN_NAME)");
  query.append(ref_constraints_join).append(" WHERE D.CONSTRAINT_NAME");
  query.append(" IS NOT NULL ");

  if (pk_table && pk_table[0])
  {
    query.append("AND A.REFERENCED_TABLE_SCHEMA = ");
    if (!pk_db.empty())
    {
      query.append("'");
      cnt = connection_proxy->real_escape_string(tmpbuff, pk_db.c_str(), (unsigned long)pk_db.length());
      query.append(tmpbuff, cnt);
      query.append("' ");
    }
    else
    {
      query.append("DATABASE() ");
    }

    query.append("AND A.REFERENCED_TABLE_NAME = '");

    cnt =connection_proxy->real_escape_string(tmpbuff, (char *)pk_table, pk_table_len);
    query.append(tmpbuff, cnt);
    query.append("' ");

    order_by = " ORDER BY PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, KEY_SEQ, FKTABLE_NAME, PKCOLUMN_NAME";
  }

  if (fk_table && fk_table[0])
  {
    query.append(" AND A.TABLE_SCHEMA = ");

    if (!fk_db.empty())
    {
      query.append("'");
      cnt = connection_proxy->real_escape_string(tmpbuff, fk_db.c_str(), (unsigned long)fk_db.length());
      query.append(tmpbuff, cnt);
      query.append("' ");
    }
    else
    {
      query.append("DATABASE() ");
    }

    query.append("AND A.TABLE_NAME = '");

    cnt = connection_proxy->real_escape_string(tmpbuff, (char *)fk_table, fk_table_len);
    query.append(tmpbuff, cnt);
    query.append("' ");

    order_by = " ORDER BY FKTABLE_CAT, FKTABLE_NAME, KEY_SEQ, PKTABLE_NAME, FKCOLUMN_NAME";
  }

  query.append(order_by);
  rc= MySQLPrepare(hstmt, (SQLCHAR *)query.c_str(), (SQLINTEGER)(query.length()),
                   true, false);

  if (!SQL_SUCCEEDED(rc))
    return rc;

  return my_SQLExecute((STMT*)hstmt);
}