Неограниченность иерархических ступеней у разделов в каталоге

chv
для того чтобы

лучше использовать отдельную функцию, структуру таблицы AlTk вообще не использовать

CREATE FUNCTION _fnLevelsWithMaxlevel ()
RETURNS @t table (
RowId int, IdParent int, LevelId int)
AS
BEGIN



declare @levelid int

set @levelid=0

insert into @t (Rowid,idparent,levelid)
select RowId,idParent,@levelid from _tblLevelsTest where idParent is null


while exists(select 1 from @t t where levelid=@levelid)
begin
insert into @t (RowId, IdParent, LevelId)
select [dbo].[_tblLevelsTest].RowId, [dbo].[_tblLevelsTest].IdParent, @levelid + 1
from [dbo].[_tblLevelsTest]
join @t t2 on ([dbo].[_tblLevelsTest].IdParent = t2.RowId)
where t2.LevelId = @levelid


set @levelid = @levelid + 1
end
return;

END

на "непромышленном" домашнем компьютере отрабатывает для 10000 записей за 0,9 сек, т.е вполне приемлимо, до промышленного сервера только завтра доберусь

 

Отлично! функция получения максимального LevelId отрабатывает за 0 сек. Можно либо сделать её join с основной таблицей (Id, IdParent), запрос отрабатывается за 1 сек. (тоже многовато, хотелось бы быстрее), либо просто ввести в эту таблицу поле MaxLevelId, которое будет одновляться в триггере при изменении двух первых полей, и тогда всё элементарно.

 

Мое IMHO

Хочется обобщить и собственные выводы по этому поводу, поскольку практически в каждом веб-приложении эта задача встает ...
Короче примерно так -
1) Реализация через указатель на родителя понятнее, проще и при небольших объемах данных (а в интернет приложениях это так в 80% ) вполне можно считать ее рекомендуемым решением. Если надо ускорить запросы , то в этом случае следует подумать о введении избыточности - поле уровня, поля пути до вершины (фактически это кэш вычисляемой информации о дереве) Она раздует БД, но и скорость выборок повысит.
Т.е. в этом варианте вставка и удаление вершин легки, а вот иерархический вывод (по честному) требует рекурсии со всеми вытекающими.

2) Реализация с помощью метода вложеных множеств (образ червяка мне неприятен ) Здесь наоборот - вывод в нужном порядке иерархии одним запросом, но вот вставка и удаление провоцируют пресчет номеров у всей той части дерева, которая "За" оперируемой вершиной. Ну и для понимания метод сложнее, но это как раз преодолимо. Соответственно,
этот метод лучше для статических данных небольшого объема.

Для примера привожу собственный ASP.NET DAL класс сделанный по 2 ому методу (результат применения его можно увидеть на http://foto.pokrovka.org/)
Правда не поручусь, что это последняя и отлаженная версия ( то что есть под рукой), но главное принцип посмотрите и нагрузку на БД.


using System;
using System.Data;
using System.Collections;
using Rsdn.Framework.Data;


namespace Sbear.PView
{
/// <summary>
/// Дерево групп
/// </summary>
public class GrpDC
{
private static string Sz;

public GrpDC()
{

}

/// <summary>
/// Получение объекта из БД по его ID
/// </summary>
/// <param name="objId">ID объекта</param>
/// <returns>Найденный объект или объект по-умолчанию</returns>
public static Grp Select(int objId)
{
Grp g;
Sz=String.Format("SELECT * FROM grp WHERE grpId={0}",objId);
g=(Grp)DbDrv.ExecBizEnt(Sz,typeof(Grp));
if (g==null){ g=new Grp();}
return g;
}

/// <summary>
/// Сохранение изменений контент-полей объекта в БД
/// </summary>
/// <param name="objId">ID объекта</param>
/// <param name="g">Объект с контент-полями</param>
/// <returns>Должно быть 1 если найден объект с таким id</returns>
public static int Update(int objId,Grp g)
{
Sz=@"UPDATE grp SET grpTitle='{0}' WHERE grpId={1}";
Sz=String.Format(Sz,g.Title,g.Id);
return DbDrv.ExecNonQuery(Sz);
}


/// <summary>
/// Вставка дочерней вершины
/// </summary>
/// <param name="plv">Левое (L) значение родителя</param>
/// <param name="ngr">Вставляемая вершина</param>
/// <returns></returns>
public static int InsertChild(int plv, Grp ngr)
{
// Раздвигаем дерево для вставки
Sz=String.Format("UPDATE grp SET grpL=grpL+2 WHERE grpL>{0}",plv);
DbDrv.ExecNonQuery(Sz);
Sz=String.Format("UPDATE grp SET grpR=grpR+2 WHERE grpR>{0}",plv);
DbDrv.ExecNonQuery(Sz);

// Вычисляем L и R значения новой вершины
ngr.L=plv+1;
ngr.R=plv+2;

//Получаем глубину вложенности
Sz=String.Format("SELECT Count(*) FROM grp WHERE grpL<{0} AND grpR>{1}",ngr.L,ngr.R);
ngr.Lvl=(int)DbDrv.ExecScalar(Sz);

// Вставляем запись
Sz=String.Format("INSERT INTO grp(grpL,grpR,grpLvl,grpTitle) VALUES ({0},{1},{2},'{3}')",ngr.L,ngr.R,ngr.Lvl,ngr.Title);
ngr.Id=DbDrv.ExecInsert(Sz);

return ngr.Id;
}

/// <summary>
/// Вставка братской вершины
/// </summary>
/// <param name="prv">Правое (R) значение исходноой вершины </param>
/// <param name="ngr">Вставляемая вершина</param>
/// <returns></returns>
public static int InsertBroth(int prv, Grp ngr)
{

// Вычисляем L и R значения новой вершины
ngr.L=prv+1;
ngr.R=prv+2;

// Раздвигаем дерево для вставки
Sz=String.Format("UPDATE grp SET grpL=grpL+2 WHERE grpL>{0}",prv);
DbDrv.ExecNonQuery(Sz);
Sz=String.Format("UPDATE grp SET grpR=grpR+2 WHERE grpR>{0}",prv);
DbDrv.ExecNonQuery(Sz);

//Получаем глубину вложенности
Sz=String.Format("SELECT Count(*) FROM grp WHERE grpL<{0} AND grpR>{1}",ngr.L,ngr.R);
ngr.Lvl=(int)DbDrv.ExecScalar(Sz);

// Вставляем запись
Sz=String.Format("INSERT INTO grp(grpL,grpR,grpLvl,grpTitle) VALUES ({0},{1},{2},'{3}')",ngr.L,ngr.R,ngr.Lvl,ngr.Title);
ngr.Id=DbDrv.ExecInsert(Sz);

return ngr.Id;
}

/// <summary>
/// Удаление вершины вместе с низлежащим поддеревом
/// </summary>
/// <param name="lv">Левое значение вершины</param>
/// <param name="rv">Правое значение вершины</param>
/// <returns>Количество удаленных вершины</returns>
public static int Delete(int lv,int rv)
{

//Удаляем поддерево
Sz=String.Format("DELETE FROM grp WHERE grpL>={0} AND grpR<={1}",lv,rv);
int res=DbDrv.ExecNonQuery(Sz);

// Уплотняем дерево
int shft=rv-lv+1; // количество удаленных номеров
Sz=String.Format("UPDATE grp SET grpL=grpL-{0} WHERE grpL>{1}",shft,lv);
DbDrv.ExecNonQuery(Sz);
Sz=String.Format("UPDATE grp SET grpR=grpR-{0} WHERE grpR>{1}",shft,lv);
DbDrv.ExecNonQuery(Sz);

return res;

}

/// <summary>
/// Путь до вершины в виде списка
/// </summary>
/// <param name="lv">Левое значение вершины</param>
/// <param name="rv">Правое значение вершины</param>
/// <returns>Список объектов Grp</returns>
public static ArrayList GetPath(int lv,int rv)
{
Sz=String.Format("SELECT * FROM Grp WHERE grpL<{0} AND grpR>{1} ORDER BY grpL ASC",lv,rv);
return DbDrv.ExecArrLst(Sz,typeof(Grp));
}

/// <summary>
/// Список объектов дерева (или поддерева)
/// </summary>
/// <param name="lv">Левое значение вершины-корня. Если=0 -все дерево целиком.</param>
/// <param name="rv">Правое значение вершины-корня</param>
/// <returns>Список объектов Grp</returns>
public static ArrayList GetTree(int lv,int rv)
{
if (lv==0)
{
Sz="SELECT * FROM Grp WHERE grpL>1 ORDER BY grpL";
}
else
{
Sz=String.Format("SELECT * FROM Grp WHERE grpL>{0} AND grpR<{1} ORDER BY grpL",lv,rv);
}
return DbDrv.ExecArrLst(Sz,typeof(Grp));
}

/// <summary>
/// Возвращает все листья дерева
/// </summary>
/// <returns>Список объектов Grp</returns>
public static ArrayList GetLeafs()
{
Sz="SELECT * FROM grp WHERE grpL=grpR-1 ORDER BY grpL";
return DbDrv.ExecArrLst(Sz,typeof(Grp));
}

/// <summary>
/// Возвращает все вершины в подуровне вершины
/// </summary>
/// <param name="pid">id родительского узла</param>
/// <returns>Список объектов Grp</returns>
public static ArrayList GetSubLevel(int pid)
{
Grp g=GrpDC.Select(pid); // получаем инфу об родительском узле

if (g.Id==0)
{Sz="SELECT * FROM grp WHERE grpLvl=0";}
else
{Sz=String.Format("SELECT * FROM grp WHERE grpL>{0} AND grpR<{1} AND grpLvl={2}",g.L,g.R,g.Lvl+1);}

return DbDrv.ExecArrLst(Sz,typeof(Grp));
}

}
}

 

silent bear,
на будущее рекомендую не использовать код запросов SQL в исходном коде.
для этого лучше использовать хранимые процедуры или представления.

ПС. в крайнем случае есть строковые ресурсы.

 

Все таки отвечу

AlTk
1) Если все внимательно посмотреть работа ведется не с конкретным провайдером, а через универсальную библиотеку. В общем случае движок БД хранимые процедуры может не поддерживать (например MySql).
2) Хранение строк в ресурсах большого выигрыша не даст -фактически запросы сосредоточены в спец. классах и редактируемы так же легко как ресурсы. К тому же в некоторых из таких классов запросы формируются динамически.
3) Остается правда главное - безопасность. Передача параметров в процедуры, конечно намного безопаснее. Но в принципе есть и другие способы бороться с SQL injection и кроссайтскриптингом. Короче типы и содержание параметров вводимых юзером желательно проверять даже с процедурами.

P.S. Кстати несколько месяцев назад видел статью сравнение производительности простых запросов инлайн и в процедурах. Ну оочень маленькая разница Оптимизировать то почти нечего в них.

НО ! С ЗАМЕЧАНИЕМ ПОЛНОСТЬЮ СОГЛАСЕН

 

silent bear, Вы забыли еще один пункт, четвертый. перед ним меркнет все остальное - и скорость работы и безопасность. это - сопровождение исходного кода, состоящее в управлении конфигурациями и изменениями.

 

стоп, предлагаемая структура БД как я понял такова:
[id] [parentId] [level]
дык, например упоминаемый RSDN выбирает (в контексте веба) детей для конкретного родителя, это не сложго, однако, на ряду с level я бы воспользовался бы и строкой [path].

но, под термином "неограниченности" я понял связку M-M, когда одна подрубрика входит в несколько подрубрик, в которые, соответственно входит несколько подрубрик (понятно выразился )
тут естественно две таблицы,
одна таблица (это уже конкретно Nekto) рубрик,
другая - связка.