From ca7fbf03a824c0bc6e43172574bb8f4e2db67ab0 Mon Sep 17 00:00:00 2001 From: Frank Brehm Date: Thu, 27 Jul 2017 15:34:09 +0200 Subject: [PATCH] Continuing adding support for poweradmin on importing PDNS data --- db/create-poweradmin-db-postgresql.sql | 1 + pp_lib/import_pdnsdata.py | 129 ++++++++++++++++++++++++- 2 files changed, 127 insertions(+), 3 deletions(-) diff --git a/db/create-poweradmin-db-postgresql.sql b/db/create-poweradmin-db-postgresql.sql index 468fc6a..78264ae 100644 --- a/db/create-poweradmin-db-postgresql.sql +++ b/db/create-poweradmin-db-postgresql.sql @@ -167,6 +167,7 @@ CREATE TABLE zones ( comment text, zone_templ_id integer NOT NULL, FOREIGN KEY(domain_id) REFERENCES domains(id), + FOREIGN KEY(owner) REFERENCES users(id), FOREIGN KEY(zone_templ_id) REFERENCES zone_templ(id) ); diff --git a/pp_lib/import_pdnsdata.py b/pp_lib/import_pdnsdata.py index 330480c..a33be38 100644 --- a/pp_lib/import_pdnsdata.py +++ b/pp_lib/import_pdnsdata.py @@ -30,7 +30,7 @@ from .common import pp, to_bool from .cfg_app import PpCfgAppError, PpConfigApplication -__version__ = '0.7.2' +__version__ = '0.7.3' LOG = logging.getLogger(__name__) # ============================================================================= @@ -412,6 +412,8 @@ class ImportPdnsdataApp(PpConfigApplication): self.import_perm_templ_items() self.import_users() self.import_zone_templ() + self.import_zone_templ_records() + self.import_zones() finally: self._close_all() @@ -505,8 +507,6 @@ class ImportPdnsdataApp(PpConfigApplication): if self.tgt_db_is_poweradmin: sequences.append('perm_templ_items_id_seq') sequences.append('zone_templ_id_seq') - sequences.append('zone_templ_records_id_seq') - sequences.append('zones_id_seq') LOG.info("Truncating all tables in target database ...") @@ -563,6 +563,20 @@ class ImportPdnsdataApp(PpConfigApplication): if not self.simulate: tgt_cursor.execute(sql, (max_id, )) + sql = "SELECT SETVAL('zone_templ_records_id_seq', 0)" + LOG.debug("Setting curval of zone_templ_records_id_seq to {} ...".format(0)) + if self.verbose > 1: + LOG.debug("SQL: {}".format(sql)) + if not self.simulate: + tgt_cursor.execute(sql, (max_id, )) + + sql = "SELECT SETVAL('zones_id_seq', 0)" + LOG.debug("Setting curval of zones_id_seq to {} ...".format(0)) + if self.verbose > 1: + LOG.debug("SQL: {}".format(sql)) + if not self.simulate: + tgt_cursor.execute(sql, (max_id, )) + LOG.debug("Commiting changes ...") self.tgt_connection.commit() @@ -1221,6 +1235,115 @@ class ImportPdnsdataApp(PpConfigApplication): LOG.debug("Commiting changes ...") self.tgt_connection.commit() + # ------------------------------------------------------------------------- + def import_zone_templ_records(self): + + LOG.info("Importing all zone template records ...") + + src_sql = textwrap.dedent('''\ + SELECT zone_templ_id, name, type, content, ttl, prio + FROM zone_templ_records + ORDER BY id + ''').strip() + if self.verbose > 1: + LOG.debug("Source SQL:\n{}".format(src_sql)) + + tgt_sql = textwrap.dedent('''\ + INSERT INTO zone_templ_records ( + zone_templ_id, name, type, content, ttl, prio) + VALUES ( + %(zone_templ_id)s, %(name)s, %(type)s, %(content)s, %(ttl)s, %(prio)) + ''').strip() + if self.verbose > 1: + LOG.debug("Target 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 > 3: + LOG.debug("Got zone templates:\n{}".format(pp(results))) + + if not results: + LOG.info("No zone templates in source database.") + LOG.debug("Commiting changes ...") + self.tgt_connection.commit() + return + + for result in results: + i += 1 + if not self.simulate: + tgt_cursor.execute(tgt_sql, result) + LOG.info("Imported {} zone templates.".format(i)) + + LOG.debug("Commiting changes ...") + self.tgt_connection.commit() + + # ------------------------------------------------------------------------- + def import_zones(self): + + LOG.info("Importing all zones ...") + + src_sql = textwrap.dedent('''\ + SELECT domain_id, owner, comment, zone_templ_id + FROM zones + WHERE domain_id IN ( + SELECT id FROM domains) + ORDER BY id + ''').strip() + if self.verbose > 1: + LOG.debug("Source SQL:\n{}".format(src_sql)) + + tgt_sql = textwrap.dedent('''\ + INSERT INTO zones ( + domain_id, owner, comment, zone_templ_id) + VALUES ( + %(domain_id)s, %(owner)s, %(comment)s, %(zone_templ_id)s) + ''').strip() + if self.verbose > 1: + LOG.debug("Target 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 > 3: + LOG.debug("Got zones:\n{}".format(pp(results))) + + if not results: + LOG.info("No zones in source database.") + LOG.debug("Commiting changes ...") + self.tgt_connection.commit() + return + + for result in results: + i += 1 + + comment = result['comment'] + if comment is not None: + comment = comment.strip() + if comment == '': + comment = None + result['comment'] = comment + + zone_templ_id = result['zone_templ_id'] + if zone_templ_id is not None and zone_templ_id == 0: + result['zone_templ_id'] = None + + if not self.simulate: + tgt_cursor.execute(tgt_sql, result) + + LOG.info("Imported {} zones.".format(i)) + + LOG.debug("Commiting changes ...") + self.tgt_connection.commit() + # ------------------------------------------------------------------------- def _close_all(self): -- 2.39.5