QFQ

import mysql.connector

import sys

import argparse

import getpass

import os

import os.path

from sshtunnel import SSHTunnelForwarder

if len(sys.argv) < 2 or not sys.argv[1].strip():

	print("Please provide a non-empty reference value.")

	sys.exit(1)

parser = argparse.ArgumentParser(description='Synchronize databases.')

parser.add_argument('reference_value', type=str, help='Reference value for the database operation.')

parser.add_argument('-b', '--backsync', action='store_true', help='Enable backsync: change first database with second database.')

args = parser.parse_args()

reference_value = args.reference_value

table_name = 'GroupMember'

search_column = 'reference'

# SSH configuration

ssh_config_1 = {

	'ssh_address_or_host': ('webwork20a.math.uzh.ch', 22),  # e.g., ('webwork20a.math.uzh.ch', 22)

	'ssh_username': 'root',

	'ssh_pkey': os.path.expanduser('~/.ssh/id_rsa'),  # e.g., "/home/user/.ssh/id_rsa"

}

ssh_config_2 = {

	'ssh_address_or_host': ('medtool.uzh.ch', 22),  # e.g., ('medtool.uzh.ch', 22)

	'ssh_username': 'root',

	'ssh_pkey': os.path.expanduser('~/.ssh/id_rsa'),  # e.g., "/home/user/.ssh/id_rsa"

}

db_name_1 = 'medtool_dev_db'

db_name_2 = 'medtool_preview_db'

db_user_1 = 'export_user'

db_user_2 = 'import_user'

db_config_1_pwd = getpass.getpass(f'Enter database password for localhost:{db_name_1}, user - {db_user_1}:')

db_config_2_pwd = getpass.getpass(f'Enter database password for localhost:{db_name_1}, user - {db_user_2}:')

# Define database configurations

db_config_1 = {

    'host': 'localhost',

    'port': '3307',

	'user': db_user_1,

	'password': db_config_1_pwd,

    'database': db_name_1

}

db_config_2 = {

    'host': 'localhost',

    'port': 3308,

	'user': db_user_2,

	'password': db_config_2_pwd,

    'database': db_name_2

}

# Change source and target database

if args.backsync:

	ssh_dummy = ssh_config_1

	ssh_config_1 = ssh_config_2

	ssh_config_2 = ssh_dummy

	db_dummy = db_config_1

	db_config_1 = db_config_2

	db_config_2 = db_dummy

# Prompt with database names

print(f"-----------------------------------------------------\nStarting {table_name} table sync with searching in column: {search_column}")

print(f"Source - Host: {ssh_config_1['ssh_address_or_host']} / DB: {db_config_1['database']}")

print(f"Target - Host: {ssh_config_2['ssh_address_or_host']} / DB: {db_config_2['database']}")

# Prompt for confirmation

confirmation = input(f"Do you really want to proceed with the reference value '{reference_value}'? (y/N): ").strip().lower()

# Check the response

if confirmation != 'y':

	print("Operation aborted by the user.")

	sys.exit(1)

# Establish the SSH tunnel for first host

with SSHTunnelForwarder(

		**ssh_config_1,

		remote_bind_address=('localhost', 3306),

		local_bind_address=('0.0.0.0', 3307)

) as tunnel1:

	# Establish the SSH tunnel for second host

	with SSHTunnelForwarder(

			**ssh_config_2,

			remote_bind_address=('localhost', 3306),

			local_bind_address=('0.0.0.0', 3308)

	) as tunnel2:

		# Establish the connections

		connection1 = mysql.connector.connect(**db_config_1)

		connection2 = mysql.connector.connect(**db_config_2)

		try:

			cursor1 = connection1.cursor(dictionary=True)

			cursor2 = connection2.cursor()

			# First check if no record exists in source database

			sql_count = f"SELECT COUNT(*) AS count_column FROM {table_name} WHERE {search_column} LIKE %s"

			cursor1.execute(sql_count, (reference_value,))

			records = cursor1.fetchone()

			records = records['count_column']

			if records == 0:

				print("No records found!")

				sys.exit()		

			# Select the specific records from the source database

			sql_select = f"SELECT * FROM {table_name} WHERE {search_column} LIKE %s"

			cursor1.execute(sql_select, (reference_value,))

			for row in cursor1:

				row.pop('id', None)

				ref_val = row[search_column]

				# Check if the record already exists in the target database

				sql_check = f"SELECT COUNT(*) FROM {table_name} WHERE {search_column} = %s"

				cursor2.execute(sql_check, (ref_val,))

				count = cursor2.fetchone()[0]

				if count == 0:

					# Insert the record

					columns = ", ".join(row.keys())

					placeholders = ", ".join(["%s"] * len(row))

					values = tuple(row.values())

					sql_insert = f"INSERT INTO {table_name} ({columns}) VALUES ({placeholders})"

					cursor2.execute(sql_insert, values)

					if cursor2.rowcount > 0:

						print("INSERT: " + ref_val)

					else:

						print("No INSERT (Something wrong): " + ref_val)

				else:

					# Update the record

					update_clauses = ", ".join(f"{col} = %s" for col in row.keys())

					values = tuple(row.values()) + (ref_val,)

					sql_update = f"UPDATE {table_name} SET {update_clauses} WHERE {search_column} = %s"

					cursor2.execute(sql_update, values)

					if cursor2.rowcount > 0:

						print("UPDATE: " + ref_val)

					else:

						print("No UPDATE (Records are identical or something went wrong): " + ref_val)

				connection2.commit()

		finally:

			cursor1.close()

			cursor2.close()

			connection1.close()

			connection2.close()

			print(f"{table_name} sync is finished!")