Skip to content

Commit 89cefb7

Browse files
lawrinlawrin
lawrin
authored and
lawrin
committed
Patch + testcase for the Bug#16277170 GETBESTROWIDENTIFIER() SHOULD CONSIDER UNIQUE NOT NULL COLUMNS TOO
1 parent 2c9c1fc commit 89cefb7

File tree

5 files changed

+118
-3
lines changed

5 files changed

+118
-3
lines changed

‎driver/mysql_metadata.cpp

Lines changed: 88 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1709,6 +1709,9 @@ MySQL_ConnectionMetaData::getBestRowIdentifier(const sql::SQLString& catalog, co
17091709

17101710
boost::scoped_ptr< sql::ResultSet > rs(getPrimaryKeys(catalog, schema, table));
17111711

1712+
if (!rs->rowsCount())
1713+
rs.reset(getUniqueNonNullableKeys(catalog, schema, table));
1714+
17121715
while (rs->next()) {
17131716
sql::SQLString columnNamePattern(rs->getString(4));
17141717

@@ -3224,6 +3227,91 @@ MySQL_ConnectionMetaData::getPrimaryKeys(const sql::SQLString& catalog, const sq
32243227
}
32253228
/* }}} */
32263229

3230+
/* {{{ MySQL_ConnectionMetaData::getUniqueNonNullableKeys() -I- */
3231+
sql::ResultSet *
3232+
MySQL_ConnectionMetaData::getUniqueNonNullableKeys(const sql::SQLString& catalog, const sql::SQLString& schema, const sql::SQLString& table)
3233+
{
3234+
CPP_ENTER("MySQL_ConnectionMetaData::getPrimaryKeys");
3235+
CPP_INFO_FMT("catalog=%s schema=%s table=%s", catalog.c_str(), schema.c_str(), table.c_str());
3236+
3237+
std::list<sql::SQLString> rs_field_data;
3238+
rs_field_data.push_back("TABLE_CAT");
3239+
rs_field_data.push_back("TABLE_SCHEM");
3240+
rs_field_data.push_back("TABLE_NAME");
3241+
rs_field_data.push_back("COLUMN_NAME");
3242+
rs_field_data.push_back("KEY_SEQ");
3243+
rs_field_data.push_back("PK_NAME");
3244+
3245+
3246+
std::auto_ptr< MySQL_ArtResultSet::rset_t > rs_data(new MySQL_ArtResultSet::rset_t());
3247+
3248+
/* Bind Problems with 49999, check later why */
3249+
if (use_info_schema && server_version > 50002) {
3250+
const sql::SQLString query("SELECT TABLE_CATALOG AS TABLE_CAT, TABLE_SCHEMA AS TABLE_SCHEM, TABLE_NAME,"
3251+
" COLUMN_NAME, SEQ_IN_INDEX AS KEY_SEQ, INDEX_NAME AS PK_NAME "
3252+
"FROM INFORMATION_SCHEMA.STATISTICS "
3253+
"WHERE TABLE_SCHEMA LIKE ? AND TABLE_NAME LIKE ? AND INDEX_NAME <> 'PRIMARY'"
3254+
" AND NON_UNIQUE = 0 AND NULLABLE <> 'YES'"
3255+
"ORDER BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX");
3256+
3257+
boost::scoped_ptr< sql::PreparedStatement > stmt(connection->prepareStatement(query));
3258+
stmt->setString(1, schema);
3259+
stmt->setString(2, table);
3260+
3261+
boost::scoped_ptr< sql::ResultSet > rs(stmt->executeQuery());
3262+
3263+
while (rs->next()) {
3264+
MySQL_ArtResultSet::row_t rs_data_row;
3265+
3266+
rs_data_row.push_back(rs->getString(1)); // TABLE_CAT
3267+
rs_data_row.push_back(rs->getString(2)); // TABLE_SCHEM
3268+
rs_data_row.push_back(rs->getString(3)); // TABLE_NAME
3269+
rs_data_row.push_back(rs->getString(4)); // COLUMN_NAME
3270+
rs_data_row.push_back(rs->getString(5)); // KEY_SEQ
3271+
rs_data_row.push_back(rs->getString(6)); // PK_NAME
3272+
3273+
rs_data->push_back(rs_data_row);
3274+
}
3275+
} else {
3276+
sql::SQLString query("SHOW KEYS FROM `");
3277+
query.append(schema).append("`.`").append(table).append("`");
3278+
3279+
boost::scoped_ptr< sql::Statement > stmt(connection->createStatement());
3280+
boost::scoped_ptr< sql::ResultSet > rs(NULL);
3281+
try {
3282+
rs.reset(stmt->executeQuery(query));
3283+
} catch (SQLException &) {
3284+
// probably schema and/or table doesn't exist. return empty set
3285+
// do nothing here
3286+
}
3287+
3288+
if (rs.get()) {
3289+
while (rs->next()) {
3290+
int non_unique = rs->getInt("Non_unique");
3291+
sql::SQLString nullable = rs->getString("Null");
3292+
if (non_unique == 0 && nullable.compare("YES")) {
3293+
sql::SQLString key_name = rs->getString("Key_name");
3294+
MySQL_ArtResultSet::row_t rs_data_row;
3295+
3296+
rs_data_row.push_back("def"); // TABLE_CAT
3297+
rs_data_row.push_back(schema); // TABLE_SCHEM
3298+
rs_data_row.push_back(rs->getString(1)); // TABLE_NAME
3299+
rs_data_row.push_back(rs->getString("Column_name")); // COLUMN_NAME
3300+
rs_data_row.push_back(rs->getString("Seq_in_index")); // KEY_SEQ
3301+
rs_data_row.push_back(key_name); // PK_NAME
3302+
3303+
rs_data->push_back(rs_data_row);
3304+
}
3305+
}
3306+
}
3307+
}
3308+
3309+
MySQL_ArtResultSet * ret = new MySQL_ArtResultSet(rs_field_data, rs_data.get(), logger);
3310+
// If there is no exception we can release otherwise on function exit memory will be freed
3311+
rs_data.release();
3312+
return ret;
3313+
}
3314+
/* }}} */
32273315

32283316
/* {{{ MySQL_ConnectionMetaData::getProcedureColumns() -U- */
32293317
sql::ResultSet *

‎driver/mysql_metadata.h

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -177,7 +177,9 @@ class MySQL_ConnectionMetaData : public sql::DatabaseMetaData
177177

178178
sql::ResultSet * getPrimaryKeys(const sql::SQLString& catalog, const sql::SQLString& schema, const sql::SQLString& table);
179179

180-
sql::ResultSet * getProcedureColumns(const sql::SQLString& catalog, const sql::SQLString& schemaPattern, const sql::SQLString& procedureNamePattern, const sql::SQLString& columnNamePattern);
180+
sql::ResultSet * getUniqueNonNullableKeys(const sql::SQLString& catalog, const sql::SQLString& schema, const sql::SQLString& table);
181+
182+
sql::ResultSet * getProcedureColumns(const sql::SQLString& catalog, const sql::SQLString& schemaPattern, const sql::SQLString& procedureNamePattern, const sql::SQLString& columnNamePattern);
181183

182184
sql::ResultSet * getProcedures(const sql::SQLString& catalog, const sql::SQLString& schemaPattern, const sql::SQLString& procedureNamePattern);
183185

‎test/unit/classes/connectionmetadata.cpp

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2122,5 +2122,27 @@ void connectionmetadata::getColumnsTypeConversions()
21222122
FAIL("See --verbose warnings!");
21232123
}
21242124

2125+
2126+
/* Simple testcase of getBestRowIdentifier returns columns making UNIQUE not Null filters
2127+
in case of primary key is not present
2128+
*/
2129+
void connectionmetadata::bestIdUniqueNotNull()
2130+
{
2131+
createSchemaObject("TABLE", "bestIdUniqueNotNull", "(id int not null, value varchar(25),"
2132+
"UNIQUE INDEX(id))");
2133+
createSchemaObject("TABLE", "bestIdUniqueNull", "(id int, value varchar(25),"
2134+
"UNIQUE INDEX(id))");
2135+
2136+
DatabaseMetaData *dbmeta= con->getMetaData();
2137+
res.reset(dbmeta->getBestRowIdentifier(con->getCatalog(), con->getSchema(), "bestIdUniqueNotNull", 0, false));
2138+
2139+
ASSERT(res->next());
2140+
ASSERT_EQUALS("id", res->getString(2));
2141+
ASSERT(!res->next());
2142+
2143+
res.reset(dbmeta->getBestRowIdentifier(con->getCatalog(), con->getSchema(), "bestIdUniqueNull", 0, false));
2144+
ASSERT(!res->next());
2145+
}
2146+
21252147
} /* namespace connectionmetadata */
21262148
} /* namespace testsuite */

‎test/unit/classes/connectionmetadata.h

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -95,6 +95,7 @@ class connectionmetadata : public unit_fixture
9595
TEST_CASE(classAttributes);
9696
*/
9797
TEST_CASE(getColumnsTypeConversions);
98+
TEST_CASE(bestIdUniqueNotNull);
9899
}
99100

100101
/**
@@ -352,6 +353,9 @@ class connectionmetadata : public unit_fixture
352353
*/
353354
void getColumnsTypeConversions();
354355

356+
357+
void bestIdUniqueNotNull();
358+
355359
};
356360

357361
REGISTER_FIXTURE(connectionmetadata);

‎test/unit/unit_fixture.cpp

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -325,8 +325,7 @@ void unit_fixture::createSchemaObject(String object_type, String object_name,
325325
dropSchemaObject(object_type, object_name);
326326

327327
String sql("CREATE ");
328-
sql.resize(object_name.length()
329-
+ object_type.length() + columns_and_other_stuff.length() + 10);
328+
330329
sql.append(object_type);
331330
sql.append(" ");
332331
sql.append(object_name);

0 commit comments

Comments
 (0)