from django.core.exceptions import ObjectDoesNotExist
def add(item,parent_name='parent'):
item = type(item).objects.get(pk=item.id)
parent = getattr(item,parent_name)
if parent:
parent = type(parent).objects.get(pk=parent.id)
if parent is None:
item.ness_level = 1
try:
item.ness_left = type(item).objects.all().order_by(
'-ness_right')[0].ness_right + 1
except ObjectDoesNotExist:
item.ness_left = 1
else:
item.ness_left = parent.ness_right
item.ness_level = parent.ness_level + 1
from django.db import connection
cursor = connection.cursor()
cursor.execute("""
UPDATE """ + item._meta.db_table + """
SET ness_right = ness_right + 2,
ness_left = IF(ness_left > %s,
ness_left + 2, ness_left)
WHERE ness_right >= %s""",
(parent.ness_right,parent.ness_right))
item.ness_right = item.ness_left + 1
item.save()
return item
def delete(item):
item = type(item).objects.get(pk=item.id)
from django.db import connection
cursor = connection.cursor()
ness_left = item.ness_left
ness_right = item.ness_right
item.delete()
# following steps is not neccessary becouse
# childre will be removed by the Django ORM
# becouse of ForeignKey relation
#cursor.execute("""
#SELECT id
#FROM """ + item._meta.db_table + """
#WHERE ness_left >= %s AND ness_right <= %s""",
#(item.ness_left,item.ness_right))
#ids = []
#for row in cursor.fetchall():
#ids.append(row[0])
#for id in ids:
#type(item).objects.get(pk=id).delete()
cursor.execute("""
UPDATE """ + item._meta.db_table + """
SET ness_left = IF(ness_left > %s,
ness_left - (%s - %s + 1),ness_left),
ness_right = ness_right - (%s - %s + 1)
WHERE ness_right > %s""",
(ness_left,
ness_right,ness_left,
ness_right,ness_left,
ness_right))
def test1(Model):
from django.db import connection
cursor = connection.cursor()
cursor.execute("""
SELECT id
FROM """ + Model._meta.db_table + """
WHERE ness_left >= ness_right""")
return 0 == len(cursor.fetchall())
def test2(Model):
from django.db import connection
cursor = connection.cursor()
cursor.execute("""
SELECT COUNT(id),MIN(ness_left),MAX(ness_right)
FROM """ + Model._meta.db_table)
vars = cursor.fetchone()
if vars[2] != vars[0] * 2:
return False
if vars[1] != 1:
return False
return True
def test3(Model):
from django.db import connection
cursor = connection.cursor()
cursor.execute("""
SELECT id
FROM """ + Model._meta.db_table + """
WHERE MOD((ness_right - ness_left),2) = 0""")
return 0 == len(cursor.fetchall())
def test4(Model):
from django.db import connection
cursor = connection.cursor()
cursor.execute("""
SELECT id
FROM """ + Model._meta.db_table + """
WHERE MOD((ness_left - ness_level + 2),2) = 1""")
return 0 == len(cursor.fetchall())
#def test5(Model):
#from django.db import connection
#cursor = connection.cursor()
#cursor.execute("""
#SELECT t1.id, COUNT(t1.id) AS rep, MAX(t3.right_key) AS max_right FROM my_tree AS t1, my_tree AS t2, my_tree AS t3 WHERE t1.left_key <> t2.left_key AND t1.left_key <> t2.right_key AND t1.right_key <> t2.left_key AND t1.right_key <> t2.right_key GROUP BY t1.id HAVING max_right <> SQRT(4 * rep + 1) + 1
#""")
#return 0 == len(cursor.fetchall())
def childs(item,include=False):
from django.db import connection
cursor = connection.cursor()
cursor.execute("""
SELECT id
FROM """ + item._meta.db_table + """
WHERE ness_left > %s AND ness_right < %s
ORDER BY ness_left""",
(item.ness_left,item.ness_right))
ids = [x[0] for x in cursor.fetchall()]
childs = type(item).objects.in_bulk(ids).values()
if include:
childs.append(item)
return childs
def parents(item,include=False):
from django.db import connection
cursor = connection.cursor()
cursor.execute("""
SELECT id
FROM """ + item._meta.db_table + """
WHERE ness_left < %s AND ness_right > %s
ORDER BY ness_left""",
(item.ness_left,item.ness_right))
ids = [x[0] for x in cursor.fetchall()]
parents = type(item).objects.in_bulk(ids).values()
if include:
parents.append(item)
return parents
#TODO: write unit tests for branch()
#def branch(item):
#from django.db import connection
#cursor = connection.cursor()
#cursor.execute("""
#SELECT id
#FROM """ + item._meta.db_table + """
#WHERE ness_right > %s AND ness_left < %s
#ORDER BY ness_left""",
#(item.ness_left,item.ness_right))
#ids = [x[0] for x in cursor.fetchall()]
#return type(item).objects.in_bulk(ids).values()
#TODO: write unit test for parent()
#def parents(item):
#from django.db import connection
#cursor = connection.cursor()
#cursor.execute("""
#SELECT id
#FROM """ + item._meta.db_table + """
#WHERE ness_left <= %s AND ness_right >= %s
# AND ness_level = %s -1""",
#(item.ness_left,item.ness_right,item.level))
#id = cursor.fetchone()[0]
#return type(item).objects.get(pk=id)