convert-powerdns-sql-to-latest-version.py

convert-powerdns-sql-to-latest-version.py

#!/usr/bin/env python2
from __future__ import print_function
import sys, re
from pprint import pprint
from collections import OrderedDict

import sys

def eprint(*args, **kwargs):
    print(*args, file=sys.stderr, **kwargs)

# Get the first argument as a filename
filename = sys.argv[1]
file = open(filename, "r")
file_contents = file.read()

# Alter Create commands with AUTO_INCREMENT
file_contents = re.sub('CREATE TABLE (`.*?`) .*? AUTO_INCREMENT=(\d*).*?;',
    r'ALTER TABLE \1 AUTO_INCREMENT = \2;',
    file_contents,
    flags=re.DOTALL);

# Remove any remaining Create commands
file_contents = re.sub(
    'CREATE.*?;','',file_contents,flags=re.DOTALL);

# Remove DROP commands
file_contents = re.sub(
    'DROP.*?;','',file_contents,flags=re.DOTALL);

# Remove ALTER commands
#file_contents = re.sub(
#    'ALTER.*?;','',file_contents,flags=re.DOTALL);

# Improve readability
file_contents = re.sub(
    '\),\(','),\n(',file_contents,flags=re.DOTALL);

# Remove unneeded table commands
unneeded_tables = ['users', 'setting', 'supermasters', 'template', 'template_record','zone_templ_records','zone_templ', 'perm_items', 'perm_templ', 'perm_templ_items', 'audit']
for table in unneeded_tables:
    file_contents = re.sub(
        "LOCK TABLES `%s`.*?UNLOCK TABLES;" %(table),'',file_contents,flags=re.DOTALL);
    file_contents = re.sub(
        "ALTER TABLE `%s`.*?;" %(table),'',file_contents,flags=re.DOTALL);

# Remove C style commands which modify extensions
#file_contents = re.sub(
#    '/.*\n','',file_contents);

# Split out each record in `records` into its own INSERT
search = "INSERT INTO `records` VALUES (\(.*?\)),{0,1}\n(\(.*\))"
match = re.findall( search, file_contents)
#match = []
while len(match) is not 0:
    file_contents = re.sub(
        search,
        r"INSERT INTO `records` VALUES \1;\nINSERT INTO `records` VALUES \2",
        file_contents);
    eprint(match[0])
    match = re.findall( search, file_contents)

# Add empty field values for records & add column names
file_contents = re.sub(
        "INSERT INTO `records` VALUES \((.*)\);",
        r"INSERT INTO `records` (id,domain_id, name, type, content, ttl, prio, change_date, disabled, ordername, auth) VALUES (\1,0,NULL,1);",
        file_contents);

# Remove duplicate records in `records`
seen = set()
answer = []
for line in file_contents.splitlines():
    match = re.findall('INSERT INTO `records`', line)

    # If the line isn't an INSERT INTO records file, ignore it and include it
    if len(match) is 0:
        answer.append(line)
        continue
    # Shorten the line to an entry that excludes the updated date and then compare the values that matter
    entry = re.sub('INSERT INTO `records` \(.*?\) VALUES \(.*?,(.*?,.*?,.*?,.*?,).*?,.*?,.*?,.*?,.*?,.*?\)',r'\1',line)
    #eprint(entry)
    if entry not in seen:
         seen.add(entry)
         answer.append(line)
    else:
        eprint("Found duplicate record %s, removing" % (entry))

file_contents =  '\n'.join(answer)

file_contents = 'use pdns;\n' + file_contents
print(file_contents)

This is intended to be executed against a mysqldump of the database of an old pdns server.