zone2sql

#!/usr/bin/python

from lib.bind import *
import subprocess
import csv

import tempfile
axfr = tempfile.NamedTemporaryFile(delete=False)
zone_axfr = axfr.name

# +----------------------------------------------------------------------+

def createSQLsoa(zone, type, f):
    with open(zone_axfr) as file:
          try:
              for line in file:
                 if re.findall( "\\b"+type+"\\b", line ):
                    fields = line.strip().split()
                    name = fields[0].strip('.')
                    ttl = fields[1]
                    type = fields[3]
                    content = fields[4].strip('.')
                    admin = fields[5].strip('.')
                    serial = fields[6]
                    refresh = fields[7]
                    retry = fields[8]
                    expire = fields[9]
                    minimum = fields[10]
                    sql = """ insert into domains (name,type,content,admin,serial,refresh,retry,expire,minimum,ttl) values ('%s','%s','%s','%s','%s',%s,%s,%s,%s,%s); """ % (name,type,content,admin,serial,refresh,retry,expire,minimum,ttl)
                    print >> f, (sql)
                    break
          except Exception, ex:
              print "Error while write SOA data"
              sys.exit(1)
# +----------------------------------------------------------------------+

def createSQLns(zone, type, f):
    with open(zone_axfr) as file:
          try:
              for line in file:
                 if re.findall( "\\b"+type+"\\b", line ):
                    fields = line.strip().split()
                    name = fields[0].strip('.')
                    ttl = fields[1]
                    type = fields[3]
                    content = fields[4].strip('.')
                    prio = '0'
                    sql = """ insert into supermasters (domain_id, name,type,content,ttl,prio) select id ,'%s', '%s', '%s', %s, %s from domains where name='%s'; """ % (zone,type,content,ttl,prio,zone)
                    print >> f, (sql)
          except Exception, ex:
              print "Error while write data 'all'"
              sys.exit(1)
    f.close

# +----------------------------------------------------------------------+

def createSQLall(zone, type, f):
    with open(zone_axfr) as file:
          try:
              for line in file:
                 if re.findall( "\\b"+type+"\\b", line ):
                    fields = line.strip().split()
                    name = fields[0].strip('.')
                    ttl = fields[1]
                    type = fields[3]
                    if type == 'MX':
                       prio = fields[4]
                       content = fields[5].strip('.')
                    else:
                       prio = '0'
                       content = fields[4].strip('.')
                    sql = """ insert into records (domain_id, name,type,content,ttl,prio) select id ,'%s', '%s', '%s', %s, %s from domains where name='%s'; """ % (name,type,content,ttl,prio,zone)
                    print >> f, (sql)
          except Exception, ex:
              print "Error while write data 'all'"
              sys.exit(1)
    f.close

# +----------------------------------------------------------------------+

def getAXFR(cmd):
    """  Run system commands """
    zone_a = open(zone_axfr, "w")
    try:
        proc = subprocess.Popen([cmd], stdout=subprocess.PIPE, shell=True)
        (out, err) = proc.communicate()
        if err == None:
            print >> zone_a, out
            return True
        else:
            print '%s zone available' % err
            return False
    except Exception, e:
        logger.error(" Shell command failed with following error: %s" % e)
        sys.exit(1)

# +----------------------------------------------------------------------+

# Run the prog to generate sql
def importZone(args):
    """ Import or dump zone information to SQL """

    jissueid = args.jiraid
    zone = args.zone.lower()

    # Jira Login
    jirauser, jirapw = jira_login()
    # Jira Validation

    # User confirm
    user_input(jirauser, 'Do you want to step through managed zone into database')

    try:
       j_valid, j_status = get_issue_by_id(serverurl,jirauser,jirapw,jissueid)
       if j_valid:
          if j_status:
             statuscode = val_status(j_status)
             logger.info("Hello %s: You are authorized to run bindadmin, '%s' status is '%s' ... -:)" % (jirauser,jissueid,statuscode))
       else:
          raise
    except Exception, e:
       logger.error("Jira issue '%s' does not exist or you don't have permission to view it, please check jira and rerun again" % jissueid)
       sys.exit(1)

    v_zone = parse_named_file(named_file, zone)
    if v_zone:
       pass
    else:
       sys.exit("Error: '%s' is not present in '%s', please check.." % (zone, named_file))

    f = open('sql/' + zone + '.sql', "w")
    cmd = 'dig @%s %s -t AXFR' % (dnsmaster, zone)
    if getAXFR(cmd):
       createSQLsoa(zone, 'SOA', f)
       createSQLns(zone, 'NS', f)
       for type in SUPPORTED_RECORD_TYPES:
           createSQLall(zone, type, f)
    else:
       print "Failed to get AXFR"
       sys.exit(1)

    if os.path.exists(zone_axfr):
       os.unlink(zone_axfr)

    print """

    **** Successful: Created dump file - sql/%s.sql ****

    NOTE: You need to manually dump/apply this to your database.

    Example:

    mysql> use dnsdb;
    mysql> \. sql/%s.sql;

    """ % (zone, zone)

# +----------------------------------------------------------------------+

def exportZone(args):
    """ Export zone information from SQL or git"""

    conn = dbconnection()
    mysql = conn.cursor()

    buffer = ""

    jissueid = args.jiraid
    zone = args.zone.lower()
    zoneid = zonedetails(zone)

    myfile = '%s' % zone

    # Jira Login
    jirauser, jirapw = jira_login()
    # Jira Validation

    # User confirm
    user_input(jirauser, 'Do you want to step through managed zone into database')

    try:
       j_valid, j_status = get_issue_by_id(serverurl,jirauser,jirapw,jissueid)
       if j_valid:
          if j_status:
             statuscode = val_status(j_status)
             logger.info("Hello %s: You are authorized to run bindadmin, '%s' status is '%s' ... -:)" % (jirauser,jissueid,statuscode))
       else:
          raise
    except Exception, e:
       logger.error("Jira issue '%s' does not exist or you don't have permission to view it, please check jira and rerun again" % jissueid)
       sys.exit(1)

    # Querying soa records
    try:
        sql = """ select * from domains where name="%s" """ % zone
        mysql.execute(sql)
        rows = ''
        rows=mysql.fetchall()
        with open(myfile, 'w') as f:
          f.write(";server localhost\n")
          f.write(";zone %s.\n" % zone)
          for fields in rows:
              zone_name = fields[1]
              type = fields[2]
              first_name_server = fields[3]
              administrative_contact = fields[4]
              zone_serial = fields[5]
              slave_refresh_interval = fields[6]
              slave_retry_interval = fields[7]
              slave_expiration_time = fields[8]
              nxdomain_cache_time = fields[9]
              record_ttl = fields[10]
              ### Begin of the output generation
              f.write("; Zone file built with the Python Tool bindmanager:\n")
              f.write("$ORIGIN %s.\n" % zone_name)
              # print("; " + __doc__.replace("\n","\n; ") )

              f.write("$TTL %s  ; Default TTL\n" % record_ttl)

              f.write("@       IN SOA  %s. %s. (\n" % (first_name_server, administrative_contact))
              f.write("         %s      ; serial\n" % zone_serial)
              f.write("         %s      ; slave refresh interval\n" % slave_refresh_interval)
              f.write("         %s      ; slave retry interval\n" % slave_retry_interval)
              f.write("         %s      ; slave copy expire time\n" % slave_expiration_time)
              f.write("         %s      ; NXDOMAIN cache time\n" % nxdomain_cache_time)
              f.write("         )")
              f.write("\n")
              f.write("\n")
              f.write("; domain name servers\n")

    except Exception, ex:
        logger.error("Fetching result - no '%s' zone available, please provide correct zone name" % zone)
        sys.exit(1)

    # Querying nameserver records
    try:
        nsql = """ select * from supermasters where domain_id='%s' """ % (zoneid)
        mysql.execute(nsql)
        result = ''
        rowsns = ''
        rowsns=mysql.fetchall()
        mysql.close()
        conn.close()
        if rowsns:
          with open(myfile, 'a') as f:
            f.write("\n")
            for fields in rowsns:
                domain_id = fields[1]
                name = fields[2]
                type = fields[3]
                content = fields[4]
                ttl = fields[5]
                f.write("@                       IN      %s              %s.\n" % (type, content))
            f.write("\n")
        else:
            raise
    except Exception, ex:
        logger.error("This may be if you have incorrect set of NS 'objects', which is not present into the zone db.")
        sys.exit(1)

    # Querying rest of the records
    conn = dbconnection()
    mysql = conn.cursor()
    try:
        rsql = """ select * from records where domain_id='%s' """ % (zoneid)
        mysql.execute(rsql)
        rowsall = ''
        rowsall=mysql.fetchall()
        mysql.close()
        conn.close()
        if rowsall:
          with open(myfile, 'a') as f:
            for fields in rowsall:
                domain_id = fields[1]
                name = fields[2]
                type = fields[3]
                content = fields[4]
                ttl = fields[5]
                name = find_hostname(zone, name)
		if name == '':
		  name = '@'
                if type == 'MX':
                  prio = fields[6]
                  f.write("@    IN    %s    %s    %s\n" % (type, prio, content))
                else:
                  f.write("$TTL %s  ; Default TTL for record\n" % ttl )
                  f.write("%s    IN    %s    %s\n" % (name, type, content))

    except Exception, ex:
        logger.error("This may be if you are trying to search incorrect records 'object'.")
        sys.exit(1)

    # Process args live
    if args.live:
        print "Step through stored zones in db: "
        ans=raw_input("Do you really want to step through the zones in the database?[N,y]: ")
        if ans in ['y','Y','yes','Yes','YES']:
          print "Thanks..."
          print "We are proceed your request to load this zone to zonepath.. please stay until it reloads"
          print "......."
        else:
          print "You typed '%s', quiting..." % ans
          sys.exit(1)

	os.system ("cp %s %s" % (zone, zonepath))
        if check_zone(zonepath, zone):
            logger.info("Sanity check went good for '%s'" % zone)
            reloadzone(zone)
            archivezone(zone)
            logger.info("Successfully exported zone '%s'" % zone)
            return True
    else:
        print """

        **** Successful: Exported zone '%s' - please check zone file at exported path ****

        NOTE: You need to manually copy/validate/reload this to your zone_path.

        Example:

        $ ls %s
        $ cp %s %s
        $ %s -t %s %s %s
        $ %s freeze %s && %s reload %s && %s thaw %s

        """ % (zone, zone, zone, zonepath, checkzone, zonepath, zone, zone, rndc, zone, rndc, zone, rndc, zone)

# +----------------------------------------------------------------------+

def main():
    """
    Figure out what you want to do from bindadmin, and then do the
    needful (at the earliest).
    """
    parser = argparse.ArgumentParser(description="Queries the zone data for just to import(dump)/export(restore) facility, we assume SQL is our central storage", epilog="To know more, write to: %s" % authoremail)
    subparsers = parser.add_subparsers()

    # zone to sql
    parser_import = subparsers.add_parser('import',help="Import or dump zone information to SQL")
    parser_import.add_argument("-z", "--zone", help="Set the zone to be dump",required=True)
    parser_import.add_argument("-j", "--jiraid", help="Jira ID for auth and request tracking",required=True)
    parser_import.set_defaults(func=importZone)

    # sql to zone file
    parser_export  = subparsers.add_parser('export', help="Export zone information from SQL or git")
    parser_export.add_argument("-z", "--zone", help="Set the zone to be restore",required=True)
    parser_export.add_argument("-j", "--jiraid", help="Jira ID for auth and request tracking",required=True)
    parser_export.add_argument("-l", "--live", action='store_true', help="Restore from DB and Put in live env to specified zone",required=False)
    parser_export.set_defaults(func=exportZone)

    args = parser.parse_args()

    args.func(args)
    return 0

# +----------------------------------------------------------------------+

if __name__ == "__main__":
    sys.exit(main())