sqldata/sqlmysqlapi.cpp
// Remove foreign key constraints referencing to the parent table
Remove foreign key constraints referencing to the parent table
Remove foreign key constraints referencing to the parent table
int SqlMysqlApi::DropReferences(const char* table, size_t *time_spent)
{
	if(table == NULL)
		return -1;
	size_t start = Os::GetTickCount();
	std::string db;
	std::string object;
	SplitQualifiedName(table, db, object);
	std::list<std::string> drop_fk;
	std::string query = "SELECT constraint_name, table_name FROM information_schema.referential_constraints ";
	query += "WHERE referenced_table_name = '";
	query += object;
	query += "'";
	int rc = _mysql_query(&_mysql, query.c_str());
	if(rc != 0)
	{
		SetError();
		return -1;
	}
	MYSQL_RES *res = _mysql_use_result(&_mysql);
	if(res == NULL)
		return -1;
	MYSQL_ROW row = NULL;
	bool more = true;
	while(more)
	{
		row = _mysql_fetch_row(res);
		if(row == NULL)
		{
			more = false;
			break;
		}
		std::string drop = "ALTER TABLE ";
		drop += row[1];
		drop += " DROP FOREIGN KEY ";
		drop += row[0];
		drop_fk.push_back(drop);
	}
	_mysql_free_result(res);
	for(std::list<std::string>::iterator i = drop_fk.begin(); i != drop_fk.end(); i++)
	{
		rc = ExecuteNonQuery((*i).c_str(), NULL);
		if(rc == -1)
			break;
	}
	if(time_spent != NULL)
		*time_spent = Os::GetTickCount() - start;
	return (rc == 0) ? 0 : -1;
}
int SqlMysqlApi :: DropReferences ( const char * table , size_t * time_spent ) { if ( table == NULL ) return -1 ; size_t start = Os :: GetTickCount ( ) ; std :: string db ; std :: string object ; SplitQualifiedName ( table , db , object ) ; std :: list < std :: string > drop_fk ; std :: string query = " " ; query += " " ; query += object ; query += " " ; int rc = _mysql_query ( & _mysql , query . c_str ( ) ) ; if ( rc != 0 ) { SetError ( ) ; return -1 ; } MYSQL_RES * res = _mysql_use_result ( & _mysql ) ; if ( res == NULL ) return -1 ; MYSQL_ROW row = NULL ; bool more = true ; while ( more ) { row = _mysql_fetch_row ( res ) ; if ( row == NULL ) { more = false ; break ; } std :: string drop = " " ; drop += row [ 1 ] ; drop += " " ; drop += row [ 0 ] ; drop_fk . push_back ( drop ) ; } _mysql_free_result ( res ) ; for ( std :: list < std :: string > :: iterator i = drop_fk . begin ( ) ; i != drop_fk . end ( ) ; i ++ ) { rc = ExecuteNonQuery ( ( * i ) . c_str ( ) , NULL ) ; if ( rc == -1 ) break ; } if ( time_spent != NULL ) * time_spent = Os :: GetTickCount ( ) - start ; return ( rc == 0 ) ? 0 : -1 ; }
Remove foreign key constraints referencing to the parent table
Remove foreign key constraints referencing to the parent table
// Table can contain the database name // Query to find foreign keys on the table (this query takes 5-8 sec on first call even if there are 3 rows in the whole table) // INNODB_STATS_ON_METADATA=0 is set to significantly speed up access to INFORMATION_SCHEMA views // (about 5-10 times faster from 5-10 sec to 0.5-1 sec). But this requires SUPER privilege and may be OFF // Execute the query // Error raised // Fetch the next row // Add drop foreign key statement // Drop foreign key constraints // If at least one FK drop fails the table cannot be dropped // mysql_query returns 1 on failure