# This file is part of the ISIS IBEX application.
# Copyright (C) 2012-2016 Science & Technology Facilities Council.
# All rights reserved.
#
# This program is distributed in the hope that it will be useful.
# This program and the accompanying materials are made available under the
# terms of the Eclipse Public License v1.0 which accompanies this distribution.
# EXCEPT AS EXPRESSLY SET FORTH IN THE ECLIPSE PUBLIC LICENSE V1.0, THE PROGRAM
# AND ACCOMPANYING MATERIALS ARE PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES
# OR CONDITIONS OF ANY KIND.  See the Eclipse Public License v1.0 for more details.
#
# You should have received a copy of the Eclipse Public License v1.0
# along with this program; if not, you can obtain a copy from
# https://www.eclipse.org/org/documents/epl-v10.php or
# http://opensource.org/licenses/eclipse-1.0.php

import mysql.connector


class SQLAbstraction(object):
    """A wrapper to connect to MySQL databases"""

    def __init__(self, dbid, user, password, host="127.0.0.1"):
        """Constructor

        Args:
            dbid (string): The id of the database that holds the required information
            user (string): The username to use to connect to the database
            password (string): The password to use to connect to the database
            host (string): The host address to use, defaults to local host
        """
        self._dbid = dbid
        self._user = user
        self._password = password
        self._host = host
        self._conn = None
        self._curs = None

    def check_db_okay(self):
        """Attempts to connect to the database and raises an error if not able to do so
        """
        try:
            self.reconnect()
            self.close_connection()
        except Exception as err:
            raise Exception(err)

    def reconnect(self):
        if self._conn is None:
            try:
                self._conn, self._curs = self.__open_connection()
            except Exception as err:
                raise Exception(err)

    def reset_connection(self):
        try:
            self.close_connection()
            self.reconnect()
        except Exception as err:
            raise Exception("Unable to reset database connection: %s" % err)

    def close_connection(self):
        if self._conn is not None:
            self._curs.close()
            self._conn.close()
        self._curs = None
        self._conn = None

    def __open_connection(self):
        """Open a connection to the database

        Returns:
            conn (mysql connector): a connection to the database
            curs (mysql cursor): a cursor to communicate with the database
        """
        conn = mysql.connector.connect(user=self._user, password=self._password, host=self._host, database=self._dbid)
        curs = conn.cursor()
        # Check db exists
        curs.execute("SHOW TABLES")
        if len(curs.fetchall()) == 0:
            # Database does not exist
            raise Exception("Requested Database %s does not exist" % self._dbid)
        return conn, curs

    def execute_query(self, query):
        """Executes a query on the database, and returns all values

        Args:
            query (string): the SQL command to run

        Returns:
            values (list): list of all rows returned
        """

        try:
            self.reconnect()
            self._curs.execute(query)
            values = self._curs.fetchall()
            return values
        except Exception as original_err:
            try:
                self.reset_connection()
            except Exception as reconnection_err:
                raise Exception ("Error executing query, database connection has been lost: %s", reconnection_err)
            raise Exception("Error executing query: %s" % original_err)

    def commit(self, query):
        try:
            self.execute_query(query)
            self._conn.commit()
        except Exception as err:
            raise Exception("Error updating database: %s" % err)
@@ -863,16 +863,66 @@
 nnector%0A
+from server_common.utilities import print_and_log%0A
 %0A%0Aclass 
@@ -1743,33 +1743,49 @@
        self.
-re
+open_
 connect
+ion_if_closed
 ()%0A         
@@ -1886,25 +1886,41 @@
     def 
-re
+open_
 connect
+ion_if_closed
 (self):%0A
@@ -2202,33 +2202,49 @@
        self.
-re
+open_
 connect
+ion_if_closed
 ()%0A        e
@@ -3194,32 +3194,44 @@
 uery(self, query
+, retry=True
 ):%0A        %22%22%22Ex
@@ -3465,25 +3465,41 @@
    self.
-re
+open_
 connect
+ion_if_closed
 ()%0A     
@@ -3632,22 +3632,156 @@
  as 
-original_err:%0A
+err:%0A            if retry:%0A                print_and_log(%22Error executing query %25s, attempting to reconnect: %25s%22 %25 (query,err), %22MINOR%22, %22DBSVR%22)%0A  
     
@@ -3780,37 +3780,43 @@
 )%0A              
+  
 try:%0A
+    
                 
@@ -3855,160 +3855,422 @@
     
-except Exception as reconnection_err:%0A                raise Exception (%22Error executing query, database connection has been lost: %25s%22, reconnection_err)
+        self.execute_query(query=query,retry=False)%0A                except Exception as reconnection_err:%0A                    err = reconnection_err%0A            raise Exception (%22Error executing query: %25s%22, err)%0A%0A    def commit(self, query, retry=True):%0A        try:%0A            self.open_connection_if_closed()%0A            self._curs.execute(query)%0A            self._conn.commit()%0A        except Exception as err:
 %0A   
@@ -4282,97 +4282,203 @@
     
-raise Exception(%22Error executing query: %25s%22 %25 original_err)%0A%0A    def commit(self, query):
+if retry:%0A                print_and_log(%22Error updating database %25s, attempting to reconnect: %25s%22 %25 (query,err), %22MINOR%22, %22DBSVR%22)%0A                try:%0A                    self.reset_connection()
 %0A   
@@ -4474,37 +4474,32 @@
 ction()%0A        
-try:%0A
             self
@@ -4522,74 +4522,124 @@
 uery
-)%0A            self._conn.commit()%0A        except Exception as err:
+=query,retry=False)%0A                except Exception as reconnection_err:%0A                    err = reconnection_err
 %0A   
@@ -4662,16 +4662,17 @@
 xception
+ 
 (%22Error 
@@ -4689,19 +4689,18 @@
 atabase: %25s%22
- %25
+,
  err)

Rearrange exception handling logic