[glom/mysql] More MySQL implementation

[Murray Cumming <murrayc src gnome org>]

commit 5b015cc78359cc34df1390a1b49dde57add5adff
Author: Murray Cumming <murrayc murrayc com>
Date:   Thu Jan 3 20:16:47 2013 +0100

    More MySQL implementation

 glom/libglom/connectionpool_backends/mysql.cc      |   10 ++-
 glom/libglom/connectionpool_backends/mysql_self.cc |   85 +++++++++++++++-----
 glom/libglom/connectionpool_backends/mysql_self.h  |    5 +-
 glom/libglom/db_utils.h                            |    1 +
 4 files changed, 74 insertions(+), 27 deletions(-)
---
diff --git a/glom/libglom/connectionpool_backends/mysql.cc b/glom/libglom/connectionpool_backends/mysql.cc
index 5ad2cfb..bb5353a 100644
--- a/glom/libglom/connectionpool_backends/mysql.cc
+++ b/glom/libglom/connectionpool_backends/mysql.cc
@@ -37,7 +37,7 @@
 #include <iostream>
 
 // Uncomment to see debug messages
-#define GLOM_CONNECTION_DEBUG
+//#define GLOM_CONNECTION_DEBUG
 
 namespace
 {
@@ -76,6 +76,12 @@ MySQL::MySQL()
 //TODO: Avoid copy/paste with Postgres::attempt_connect()
 Glib::RefPtr<Gnome::Gda::Connection> MySQL::attempt_connect(const Glib::ustring& port, const Glib::ustring& database, const Glib::ustring& username, const Glib::ustring& password, bool fake_connection)
 {
+  if(database.empty())
+  {
+    std::cerr << G_STRFUNC << ": The database name is empty. This is strange." << std::endl;
+    return Glib::RefPtr<Gnome::Gda::Connection>();
+  }
+
   const Glib::ustring default_database = "INFORMATION_SCHEMA";
   //const Glib::ustring& actual_database = (!database.empty()) ? database : default_database;;
   const Glib::ustring cnc_string_main = "HOST=" + DbUtils::gda_cnc_string_encode(m_host)
@@ -130,7 +136,7 @@ Glib::RefPtr<Gnome::Gda::Connection> MySQL::attempt_connect(const Glib::ustring&
     catch(const Glib::Error& /* ex */)
     {
       //Show this on stderr because it can contain useful clues such as a hostname that cannot be resolved.
-      //std::cerr << G_STRFUNC << ": Attempt to connect to default database failed on port=" << port << " : " << "error code=" << ex.code() << ", error message: " <<  ex.what() << std::endl;
+      std::cerr << G_STRFUNC << ": Attempt to connect to default database failed on port=" << port << " : " << "error code=" << ex.code() << ", error message: " <<  ex.what() << std::endl;
     }
 
 #ifdef GLOM_CONNECTION_DEBUG
diff --git a/glom/libglom/connectionpool_backends/mysql_self.cc b/glom/libglom/connectionpool_backends/mysql_self.cc
index 49b0c85..3d18cb5 100644
--- a/glom/libglom/connectionpool_backends/mysql_self.cc
+++ b/glom/libglom/connectionpool_backends/mysql_self.cc
@@ -66,6 +66,8 @@ static const int PORT_MYSQL_SELF_HOSTED_END = 3350;
 static const char FILENAME_DATA[] = "data";
 static const char FILENAME_BACKUP[] = "backup";
 
+static const char DEFAULT_DATABASE_NAME[] = "INFORMATION_SCHEMA";
+
 MySQLSelfHosted::MySQLSelfHosted()
 : m_network_shared(false),
   m_temporary_password_active(false)
@@ -187,7 +189,7 @@ Backend::InitErrors MySQLSelfHosted::initialize(const SlotProgress& slot_progres
   }
   else
   {
-    std::cout << "debug: command_initdb succeeded" << ", this=" << this << std::endl;
+    //std::cout << "debug: command_initdb succeeded" << ", this=" << this << std::endl;
   
     //This is used during the first start:
     m_initial_password_to_set = password;
@@ -196,6 +198,8 @@ Backend::InitErrors MySQLSelfHosted::initialize(const SlotProgress& slot_progres
     //TODO: With MySQL 5.6, use the new --random-passwords option (see above)
     m_temporary_password = "";
     m_temporary_password_active = true;
+    m_saved_username = "root";
+    m_saved_password = "";
 
     //Startup (and shutdown) so we can set the initial username and password.
     //TODO: This is inefficient, because the caller probably wants to start the server soon anyway,
@@ -251,15 +255,33 @@ float MySQLSelfHosted::get_mysqlql_utils_version_as_number(const SlotProgress& /
   return 0; //TODO
 }
 
-static Glib::ustring build_query_change_username(const Glib::ustring& old_username, const Glib::ustring& new_username)
+static Glib::ustring build_query_change_username(const Glib::RefPtr<Gnome::Gda::Connection>& connection, const Glib::ustring& old_username, const Glib::ustring& new_username)
 {
-  return "RENAME USER " + DbUtils::escape_sql_id(old_username) + " TO " + DbUtils::escape_sql_id(new_username);
+  if(old_username.empty())
+  {
+    std::cerr << G_STRFUNC << ": old_username is empty." << std::endl;
+    return Glib::ustring();
+  }
+
+  if(new_username.empty())
+  {
+    std::cerr << G_STRFUNC << ": new_username is empty." << std::endl;
+    return Glib::ustring();
+  }
+
+  //TODO: Try to avoid specifing @localhost.
+  //We do this to avoid this error:
+  //mysql> RENAME USER root TO glom_dev_user;
+  //ERROR 1396 (HY000): Operation RENAME USER failed for 'root'@'%'
+  //mysql> RENAME USER root localhost TO glom_dev_user;
+  //Query OK, 0 rows affected (0.00 sec)
+  const Glib::ustring user = connection->quote_sql_identifier(old_username) + "@localhost";
+
+  return "RENAME USER " + user + " TO " + connection->quote_sql_identifier(new_username);
 }
 
 Backend::StartupErrors MySQLSelfHosted::startup(const SlotProgress& slot_progress, bool network_shared)
 {
-  std::cout << G_STRFUNC << std::cout << "  DEBUG0: m_temporary_password_active=" << m_temporary_password_active << std::endl;
-
   m_network_shared = network_shared;
 
   // Don't risk random crashes, although this really shouldn't be called
@@ -298,7 +320,7 @@ Backend::StartupErrors MySQLSelfHosted::startup(const SlotProgress& slot_progres
     }
     else
     {
-      std::cerr << "ConnectionPool::create_self_hosting(): The data sub-directory could not be found." << dbdir_data_uri << std::endl;
+      std::cerr << G_STRFUNC << ": The data sub-directory could not be found." << dbdir_data_uri << std::endl;
       return STARTUPERROR_FAILED_NO_DATA;
     }
   }
@@ -331,7 +353,7 @@ Backend::StartupErrors MySQLSelfHosted::startup(const SlotProgress& slot_progres
   std::cout << G_STRFUNC << ": debug: command_mysql_start=" << command_mysql_start << std::endl;
 
   m_port = available_port; //Needed by get_mysqladmin_command().
-  const std::string command_check_mysql_has_started = get_mysqladmin_command(m_temporary_password) //TODO: Get the temporary password in a callback.
+  const std::string command_check_mysql_has_started = get_mysqladmin_command(m_saved_username, m_saved_password) //TODO: Get the temporary password in a callback.
     + " ping";
   const std::string second_command_success_text = "mysqld is alive"; //TODO: This is not a stable API. Also, watch out for localisation.
   std::cout << G_STRFUNC << ": debug: command_check_mysql_has_started=" << command_check_mysql_has_started << std::endl;
@@ -353,14 +375,12 @@ Backend::StartupErrors MySQLSelfHosted::startup(const SlotProgress& slot_progres
   if(m_temporary_password_active)
   {
     //Set the root password:
-    const std::string command_initdb_set_initial_password = get_mysqladmin_command(m_temporary_password)
+    const std::string command_initdb_set_initial_password = get_mysqladmin_command("root", m_temporary_password)
       + " password " + Glib::shell_quote(m_initial_password_to_set);
-    std::cout << "debug: command_initdb_set_initial_password=" << command_initdb_set_initial_password << std::endl;
+    //std::cout << "debug: command_initdb_set_initial_password=" << command_initdb_set_initial_password << std::endl;
 
     const bool result = Glom::Spawn::execute_command_line_and_wait(command_initdb_set_initial_password, slot_progress);
 
-    std::cout << G_STRFUNC << std::cout << "  DEBUG4" << std::endl;
-
     if(!result)
     {
       std::cerr << "Error while attempting to start self-hosting MySQL database, when setting the initial password." << std::endl;
@@ -370,30 +390,32 @@ Backend::StartupErrors MySQLSelfHosted::startup(const SlotProgress& slot_progres
     m_temporary_password_active = false;
     m_temporary_password.clear();
 
-  std::cout << G_STRFUNC << std::cout << "  DEBUG5" << std::endl;
-
-
     //Rename the root user,
     //so we can connnect as the expected username:
     //We connect to the INFORMATION_SCHEMA database, because libgda needs us to specify some database.
-    Glib::RefPtr<Gnome::Gda::Connection> gda_connection = connect("INFORMATION_SCHEMA", "root", m_initial_password_to_set);
+    const Glib::RefPtr<Gnome::Gda::Connection> gda_connection = connect(DEFAULT_DATABASE_NAME, "root", m_initial_password_to_set);
     if(!gda_connection)
     {
       std::cerr << G_STRFUNC << "Error while attempting to start self-hosting MySQL database, when setting the initial username: connection failed." << std::endl;
       return STARTUPERROR_FAILED_UNKNOWN_REASON;
     }
+    m_saved_password = m_initial_password_to_set;
 
-    std::cout << G_STRFUNC << std::cout << "  DEBUG6" << std::endl;
+    const std::string query = build_query_change_username(gda_connection, "root", m_initial_username_to_set);
+    //std::cout << G_STRFUNC << std::cout << "  DEBUG: rename user query=" << query << std::endl;
 
-    const std::string query = build_query_change_username("root", m_initial_username_to_set);
     try
     {
-      const bool test = gda_connection->statement_execute_non_select(query);
+      /* const bool test = */ gda_connection->statement_execute_non_select(query);
+      //This returns false even when the UPDATE succeeded,
+      //but throws an exception when it fail.
+      /*
       if(!test)
       {
         std::cerr << G_STRFUNC << "Error while attempting to start self-hosting MySQL database, when setting the initial username: UPDATE failed." << std::endl;
        return STARTUPERROR_FAILED_UNKNOWN_REASON;
       }
+      */
     }
     catch(const Glib::Error& ex)
     {
@@ -402,6 +424,7 @@ Backend::StartupErrors MySQLSelfHosted::startup(const SlotProgress& slot_progres
     }
   }
 
+  m_saved_username = m_initial_username_to_set;
   m_initial_username_to_set.clear();
 
   return STARTUPERROR_NONE;
@@ -410,6 +433,7 @@ Backend::StartupErrors MySQLSelfHosted::startup(const SlotProgress& slot_progres
 //TODO: Avoid copy/paste with PostgresSelfHosted:
 void MySQLSelfHosted::show_active_connections()
 {
+/* TODO_MySQL
   Glib::RefPtr<Gnome::Gda::SqlBuilder> builder =
       Gnome::Gda::SqlBuilder::create(Gnome::Gda::SQL_STATEMENT_SELECT);
   builder->select_add_field("*", "pg_stat_activity");
@@ -444,17 +468,23 @@ void MySQLSelfHosted::show_active_connections()
   
   //Make sure that this connection does not stop a further attempt to stop the server.
   gda_connection->close();
+*/
 }
 
-std::string MySQLSelfHosted::get_mysqladmin_command(const Glib::ustring& password)
+std::string MySQLSelfHosted::get_mysqladmin_command(const Glib::ustring& username, const Glib::ustring& password)
 {
+  if(username.empty())
+  {
+    std::cerr << G_STRFUNC << ": username is empty." << std::endl;
+  }
+
   const std::string port_as_text = Glib::Ascii::dtostr(m_port);
 
   std::string command = get_path_to_mysql_executable("mysqladmin")
     + " --no-defaults"
     + " --port=" + port_as_text
     + " --protocol=tcp" //Otherwise we cannot connect as root. TODO: However, maybe we could use --skip-networking if network sharing is not enabled.
-    + " --user=root";
+    + " --user=" + Glib::shell_quote(username);
 
   //--password='' is not always interpreted the same as specifying no --password.
   if(!password.empty())
@@ -470,7 +500,10 @@ bool MySQLSelfHosted::cleanup(const SlotProgress& slot_progress)
   //g_assert(get_self_hosting_active());
 
   if(!get_self_hosting_active())
+  {
+    //std::cout << G_STRFUNC << ": self-hosting is not active." << std::endl;
     return true; //Don't try to stop it if we have not started it.
+  }
 
   const std::string port_as_text = Glib::Ascii::dtostr(m_port);
 
@@ -481,9 +514,12 @@ bool MySQLSelfHosted::cleanup(const SlotProgress& slot_progress)
   // TODO: Warn about connected clients on other computers? Warn those other users?
   // Make sure to use double quotes for the executable path, because the
   // CreateProcess() API used on Windows does not support single quotes.
-  const std::string command_mysql_stop = get_mysqladmin_command("" /* m_password */)
+  const std::string command_mysql_stop = get_mysqladmin_command(m_saved_username, m_saved_password)
     + " shutdown";
+  std::cout << "DEBUGcleanup before shutdown: command=" << command_mysql_stop << std::endl;
   const bool result = Glom::Spawn::execute_command_line_and_wait(command_mysql_stop, slot_progress);
+  std::cout << "DEBUGcleanup after shutdown" << std::endl;
+
   if(!result)
   {
     std::cerr << "Error while attempting to stop self-hosting of the MySQL database. Trying again."  << std::endl;
@@ -536,6 +572,12 @@ static bool on_timeout_delay(const Glib::RefPtr<Glib::MainLoop>& mainloop)
 
 Glib::RefPtr<Gnome::Gda::Connection> MySQLSelfHosted::connect(const Glib::ustring& database, const Glib::ustring& username, const Glib::ustring& password, bool fake_connection)
 {
+  if(database.empty())
+  {
+    std::cerr << G_STRFUNC << ": The database name is empty. This is strange." << std::endl;
+    return Glib::RefPtr<Gnome::Gda::Connection>();
+  }
+
   if(!get_self_hosting_active())
   {
     throw ExceptionConnection(ExceptionConnection::FAILURE_NO_BACKEND); //TODO: But there is a backend. It's just not ready.
@@ -551,7 +593,6 @@ Glib::RefPtr<Gnome::Gda::Connection> MySQLSelfHosted::connect(const Glib::ustrin
   {
     try
     {
-      std::cout << "DEBUG: Calling attempt_connect(): username=" << username << ", password=" << password << std::endl;
       result = attempt_connect(port_as_string(m_port), database, username, password, fake_connection);
     }
     catch(const ExceptionConnection& ex)
diff --git a/glom/libglom/connectionpool_backends/mysql_self.h b/glom/libglom/connectionpool_backends/mysql_self.h
index ac46f60..828bd0f 100644
--- a/glom/libglom/connectionpool_backends/mysql_self.h
+++ b/glom/libglom/connectionpool_backends/mysql_self.h
@@ -55,7 +55,7 @@ public:
   static bool install_mysql(const SlotProgress& slot_progress);
 
 private:
-  std::string get_mysqladmin_command(const Glib::ustring& password);
+  std::string get_mysqladmin_command(const Glib::ustring& username, const Glib::ustring& password);
 
   virtual InitErrors initialize(const SlotProgress& slot_progress, const Glib::ustring& initial_username, const Glib::ustring& password, bool network_shared = false);
 
@@ -85,8 +85,7 @@ private:
 
   bool m_network_shared;
   
-  //These are only remembered in order to use them to provide debug
-  //information when the MySQL shutdown fails:
+  //These are remembered in order to use them to issue the shutdown command via mysqladmin:
   Glib::ustring m_saved_database_name, m_saved_username, m_saved_password;
 
   bool m_temporary_password_active; //Whether the password is an initial temporary one.
diff --git a/glom/libglom/db_utils.h b/glom/libglom/db_utils.h
index 986cc35..408296b 100644
--- a/glom/libglom/db_utils.h
+++ b/glom/libglom/db_utils.h
@@ -174,6 +174,7 @@ bool rename_table(const Glib::ustring& table_name, const Glib::ustring& new_tabl
 bool drop_table(const Glib::ustring& table_name);
 
 /** Escape, and quote, SQL identifiers such as table names.
+ * This requires a current connection.
  */
 Glib::ustring escape_sql_id(const Glib::ustring& id);