From f8f083987c97909ef05b6a0bb6355dd70927cf86 Mon Sep 17 00:00:00 2001 From: Frank Brehm Date: Mon, 10 Jul 2017 18:49:45 +0200 Subject: [PATCH] Importing cryptokeys and domainmetadata --- pp_lib/import_pdnsdata.py | 138 ++++++++++++++++++++++++++++++++++++-- 1 file changed, 133 insertions(+), 5 deletions(-) diff --git a/pp_lib/import_pdnsdata.py b/pp_lib/import_pdnsdata.py index 5c02979..2e5a047 100644 --- a/pp_lib/import_pdnsdata.py +++ b/pp_lib/import_pdnsdata.py @@ -29,7 +29,7 @@ from .common import pp from .cfg_app import PpCfgAppError, PpConfigApplication -__version__ = '0.4.0' +__version__ = '0.4.1' LOG = logging.getLogger(__name__) # ============================================================================= @@ -113,6 +113,8 @@ class ImportPdnsdataApp(PpConfigApplication): self.get_src_info() self.clean_tgt_db() self.import_domains() + self.import_cryptokeys() + self.import_domainmetadata() finally: self._close_all() @@ -213,7 +215,7 @@ class ImportPdnsdataApp(PpConfigApplication): ORDER by name ''').strip() if self.verbose > 1: - LOG.debug("Source SQL: {}".format(src_sql)) + LOG.debug("Source SQL:\n{}".format(src_sql)) tgt_sql = textwrap.dedent('''\ INSERT INTO domains (id, name, master, last_check, type, notified_serial, account) @@ -221,12 +223,13 @@ class ImportPdnsdataApp(PpConfigApplication): %(type)s, %(notified_serial)s, %(account)s) ''').strip() if self.verbose > 1: - LOG.debug("Source SQL: {}".format(tgt_sql)) + LOG.debug("Source SQL:\n{}".format(tgt_sql)) with self.tgt_connection.cursor() as tgt_cursor: with self.src_connection.cursor() as src_cursor: + i = 0 src_cursor.execute(src_sql) results = src_cursor.fetchall() @@ -234,10 +237,12 @@ class ImportPdnsdataApp(PpConfigApplication): LOG.debug("Got domains:\n{}".format(pp(results))) for result in results: + i += 1 tgt_cursor.execute(tgt_sql, result) + LOG.info("Imported {} domains.".format(i)) LOG.debug("Get max. Domain Id ...") - sql = "SELECT MAX(id) AS max_id from domains" + sql = "SELECT MAX(id) AS max_id FROM domains" if self.verbose > 1: LOG.debug("SQL: {}".format(sql)) tgt_cursor.execute(sql) @@ -245,7 +250,7 @@ class ImportPdnsdataApp(PpConfigApplication): if self.verbose > 2: LOG.debug("Got max domain Id:\n{}".format(pp(result))) max_id = int(result[0]) - sql = "select setval('domains_id_seq', %s)" + sql = "SELECT SETVAL('domains_id_seq', %s)" LOG.debug("Setting curval of domains_id_seq to {} ...".format(max_id)) if self.verbose > 1: LOG.debug("SQL: {}".format(sql)) @@ -254,6 +259,129 @@ class ImportPdnsdataApp(PpConfigApplication): LOG.debug("Commiting changes ...") self.tgt_connection.commit() + # ------------------------------------------------------------------------- + def import_cryptokeys(self): + + LOG.info("Importing all cryptokeys ...") + + src_sql = textwrap.dedent('''\ + SELECT id, domain_id, flags, active, content + FROM cryptokeys + ''').strip() + if self.verbose > 1: + LOG.debug("Source SQL:\n{}".format(src_sql)) + + tgt_sql = textwrap.dedent('''\ + INSERT INTO cryptokeys (id, domain_id, flags, active, content) + VALUES (%(id)s, %(domain_id)s, %(flags)s, %(active)s, %(content)s) + ''').strip() + if self.verbose > 1: + LOG.debug("Source SQL:\n{}".format(tgt_sql)) + + + with self.tgt_connection.cursor() as tgt_cursor: + with self.src_connection.cursor() as src_cursor: + + src_cursor.execute(src_sql) + results = src_cursor.fetchall() + + if self.verbose > 2: + LOG.debug("Got cryptokeys:\n{}".format(pp(results))) + + if not results: + LOG.info("No cryptokeys in source database.") + LOG.debug("Commiting changes ...") + self.tgt_connection.commit() + return + + i = 0 + for result in results: + i += 1 + if result['active']: + result['active'] = True + else: + result['active'] = False + tgt_cursor.execute(tgt_sql, result) + LOG.info("Imported {} cryptokeys.".format(i)) + + LOG.debug("Get max. CryptoKey Id ...") + sql = "SELECT MAX(id) AS max_id FROM cryptokeys" + if self.verbose > 1: + LOG.debug("SQL: {}".format(sql)) + tgt_cursor.execute(sql) + result = tgt_cursor.fetchone() + if self.verbose > 2: + LOG.debug("Got max cryptokey Id:\n{}".format(pp(result))) + max_id = int(result[0]) + sql = "SELECT SETVAL('cryptokeys_id_seq', %s)" + LOG.debug("Setting curval of cryptokeys_id_seq to {} ...".format(max_id)) + if self.verbose > 1: + LOG.debug("SQL: {}".format(sql)) + tgt_cursor.execute(sql, (max_id, )) + + LOG.debug("Commiting changes ...") + self.tgt_connection.commit() + + # ------------------------------------------------------------------------- + def import_domainmetadata(self): + + LOG.info("Importing all domainmetadata ...") + + src_sql = textwrap.dedent('''\ + SELECT id, domain_id, kind, content + FROM domainmetadata + WHERE domain_id IN ( + SELECT id FROM domains) + ''').strip() + if self.verbose > 1: + LOG.debug("Source SQL:\n{}".format(src_sql)) + + tgt_sql = textwrap.dedent('''\ + INSERT INTO domainmetadata (id, domain_id, kind, content) + VALUES (%(id)s, %(domain_id)s, %(kind)s, %(content)s) + ''').strip() + if self.verbose > 1: + LOG.debug("Source SQL:\n{}".format(tgt_sql)) + + + with self.tgt_connection.cursor() as tgt_cursor: + with self.src_connection.cursor() as src_cursor: + + i = 0 + src_cursor.execute(src_sql) + results = src_cursor.fetchall() + + if self.verbose > 2: + LOG.debug("Got domainmetadata:\n{}".format(pp(results))) + + if not results: + LOG.info("No domainmetadata in source database.") + LOG.debug("Commiting changes ...") + self.tgt_connection.commit() + return + + for result in results: + i += 1 + tgt_cursor.execute(tgt_sql, result) + LOG.info("Imported {} domainmetadata.".format(i)) + + LOG.debug("Get max. DomainMetadata Id ...") + sql = "SELECT MAX(id) AS max_id FROM domainmetadata" + if self.verbose > 1: + LOG.debug("SQL: {}".format(sql)) + tgt_cursor.execute(sql) + result = tgt_cursor.fetchone() + if self.verbose > 2: + LOG.debug("Got max domainmetadata Id:\n{}".format(pp(result))) + max_id = int(result[0]) + sql = "SELECT SETVAL('domainmetadata_id_seq', %s)" + LOG.debug("Setting curval of domainmetadata_id_seq to {} ...".format(max_id)) + if self.verbose > 1: + LOG.debug("SQL: {}".format(sql)) + tgt_cursor.execute(sql, (max_id, )) + + LOG.debug("Commiting changes ...") + self.tgt_connection.commit() # ------------------------------------------------------------------------- def _close_all(self): -- 2.39.5