Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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)
@Sam-Martin

This comment has been minimized.

Copy link
Owner Author

@Sam-Martin Sam-Martin commented Jun 7, 2018

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment