Rebuilds an MPTT-style tree structure in a database table.

treefix.py

# Uses PySQLPool (http://code.google.com/p/pysqlpool/) for the database connection.
from PySQLPool.PySQLConnection import PySQLConnection
from PySQLPool.PySQLQuery import PySQLQuery

import urllib2
import threading
import sys

dbconn = PySQLConnection(host='localhost',user='user',password='password',port=3306,db='schema',charset='utf8')
query = PySQLQuery(dbconn, commitOnEnd=True)

def getChildren(parent_id):
	query.Query('select id from `%s` where parent_id=%s' % (tree_table, parent_id))
	return query.record

class Node:
	def __init__(self, id):
		self.id = id
		self.lft = 0
		self.rght = 0
		self.children = []
		
	def populate(self):
		for child in getChildren(self.id):
			self.children.append(Node(child['id']))
			
		for child in self.children:
			child.populate()
		
	@property
	def child_count(self):
		kids = 0
		for child in self.children:
			kids += child.child_count
		kids = kids + len(self.children)
		return kids
	
	def setLftRght(self):
		global lft
		self.lft = lft
		lft = lft + 1
		
		self.rght = self.lft + (self.child_count*2) + 1
		query.Query('update `%s` set lft=%s, rght=%s where id=%s' % (tree_table, self.lft, self.rght, self.id))
		
		for child in self.children:
			child.setLftRght()
				
		lft = lft + 1 # To account for this node's rght
		

tree_table = 'my_table'		
query.Query('update `%s` set lft=0,rght=0' % tree_table)
query.Query('select id from `%s` where parent_id=0' % tree_table)
tree_parent_id = query.record[0]['id']
tree = Node(tree_parent_id)
tree.populate()

lft = 0
tree.setLftRght()