spacepaste

  1.  
  2. # pylint: disable=too-many-arguments, import-error
  3. """
  4. :mod:`nyx_schema_compare` -- compare mysql schemas
  5. ------------------------------------------------------
  6. Todo: Add more
  7. """
  8. import sys
  9. from difflib import unified_diff
  10. import click
  11. import pymysql
  12. from sshtunnel import SSHTunnelForwarder
  13. from tabulate import tabulate
  14. from .nyxgateway import NyxGateway
  15. class MySqlConnection:
  16. def __init__(self, host="localhost", user="test", passwd="test",
  17. database_name="test1", is_vagrant=False):
  18. self.database_name = database_name
  19. self.tunnel = SSHTunnelForwarder(
  20. ('localhost', 2222),
  21. ssh_username="vagrant",
  22. ssh_password="vagrant",
  23. remote_bind_address=('127.0.0.1', 3306)
  24. )
  25. try:
  26. self.gateway = NyxGateway()
  27. self.tunnel.start() if is_vagrant else None
  28. self.connection = pymysql.connect(host=host, user=user,
  29. passwd=passwd,
  30. db=database_name)
  31. self.cursor = self.connection.cursor()
  32. # If anything fails, we exit out immediately
  33. except Exception as error:
  34. self.clean_up(error)
  35. def __del__(self):
  36. self.tunnel.close() if self.tunnel.is_alive else None
  37. def get_table_names(self):
  38. try:
  39. self.cursor.execute("""
  40. SELECT TABLE_NAME
  41. FROM INFORMATION_SCHEMA.TABLES
  42. WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA=%s
  43. """, (self.database_name,))
  44. return [element for tupl in self.cursor.fetchall() for element in
  45. tupl]
  46. except Exception as error:
  47. self.clean_up(error)
  48. def get_create_table_query(self, table_name):
  49. try:
  50. self.cursor.execute("""
  51. SHOW CREATE TABLE {}
  52. """.format(table_name))
  53. data = self.cursor.fetchone()
  54. return data[1].split("\n") if data[1] and isinstance(data[1],
  55. str) else ''
  56. except Exception as error:
  57. self.clean_up(error.args[1])
  58. except Exception as error:
  59. self.clean_up(error)
  60. def clean_up(self, error):
  61. self.cursor.close()
  62. self.connection.close()
  63. print(error)
  64. sys.exit(1)
  65. class Utilities:
  66. @classmethod
  67. def get_diff_from_lists(cls, primary, comparator):
  68. return list(set(comparator) - set(primary))
  69. @classmethod
  70. def get_common_from_lists(cls, comparator_a, comparator_b):
  71. return list(set(comparator_a) & set(comparator_b))
  72. class TableInfo:
  73. def __init__(self, alias_name, gateway=NyxGateway()):
  74. self.gateway = gateway
  75. self.profile = self.gateway.get_schema_profile(alias_name)
  76. self.connection = MySqlConnection(**self.profile)
  77. self.tables = self.connection.get_table_names()
  78. class Main:
  79. ADDITIONAL = 'additional'
  80. MISSING = 'missing'
  81. COMMON = 'common'
  82. def __init__(self, primary_alias, comparator_alias, gateway=NyxGateway()):
  83. self.primary_table = TableInfo(primary_alias)
  84. self.comparator_table = TableInfo(comparator_alias)
  85. self.table_comparison_data = self.get_differences_in_schema_tables(
  86. self.primary_table.tables,
  87. self.comparator_table.tables)
  88. self.gateway = gateway
  89. self.primary_alias = primary_alias
  90. self.comparator_alias = comparator_alias
  91. def print_results(self):
  92. print(tabulate({
  93. "Primary": [
  94. self.primary_table.profile["host"],
  95. self.primary_table.profile["database_name"],
  96. self.primary_table.profile["user"],
  97. ],
  98. "Comparator": [
  99. self.comparator_table.profile["host"],
  100. self.comparator_table.profile["database_name"],
  101. self.comparator_table.profile["user"]
  102. ]
  103. }, headers="keys", tablefmt="grid"), "\n")
  104. try:
  105. # Print table common, missing, and additional
  106. print(tabulate(self.table_comparison_data, headers="keys",
  107. tablefmt="grid"), "\n")
  108. # Print diffs for each table create sql query
  109. for table in self.table_comparison_data['common']:
  110. for line in unified_diff(
  111. self.primary_table.connection.get_create_table_query(
  112. table),
  113. self.comparator_table.connection.get_create_table_query(
  114. table),
  115. "Primary: {}:{}".format(self.primary_alias, table),
  116. "Comparator: {}:{}".format(self.comparator_alias,
  117. table)):
  118. print(line)
  119. except Exception as error:
  120. print(error)
  121. def get_differences_in_schema_tables(self, primary, comparator):
  122. return {
  123. self.COMMON: Utilities.get_common_from_lists(primary, comparator),
  124. self.MISSING: Utilities.get_diff_from_lists(primary, comparator),
  125. self.ADDITIONAL: Utilities.get_diff_from_lists(comparator, primary)
  126. }
  127. @click.command()
  128. @click.argument('primary_alias')
  129. @click.argument('comparator_alias')
  130. def nyx_schema_compare(primary_alias, comparator_alias):
  131. main = Main(primary_alias, comparator_alias)
  132. main.print_results()
  133. if __name__ == "__main__":
  134. nyx_schema_compare()
  135.