#!/usr/bin/perl

# Скрипт нумерует поля l и r в таблице kladr в соответствии с принципом nested sets

use DBI;
use strict;
use vars qw($index $dbho $nextcommit);

$dbho = DBI->connect("dbi:Pg:dbname=dbname;host=127.0.0.1", "dbusername", "dbpass", {AutoCommit => 0, RaiseError => 1, PrintError => 1});
$dbho->commit;

$index = 0;
$nextcommit = 1000;

do_tree('');

$dbho->commit;
$dbho->disconnect;

sub do_tree
{
    my $owner = $_[0];

    my $c;
    if ($owner eq '')
    {
	$c = $dbho->prepare('select id from kladr where owner is NULL order by name');
	$c->execute();
    }
    else
    {
	$c = $dbho->prepare('select id from kladr where owner = ? order by name');
	$c->execute($owner);
    };
    while (my ($id) = $c->fetchrow_array())
    {
	$index++;
	my $l = $index;
	do_tree($id);
	$index++;
	my $r = $index;
	$dbho->do('update kladr set l = ?, r = ? where id = ?', undef, $l, $r, $id);
	print $index, "\n";
    };
    $c->finish;
    if ($index >= $nextcommit)
    {
	$dbho->commit();
	$nextcommit = $index + 1000;
    };
};
