Форум MySQL

Таблица типа MEMORY содержит 2000 таких строк:

INT id PRIMARY, INT mother_ID, INT father_ID

Задача:
Для id = x подобрать одну(любую) невесту не находящуюся с ним в родстве вплоть до 7-го колена.

Разумеется, mother_ID и father_ID - это ссылки id на родителей.



P.S. Spatial итд не надо предлагать.(Для типа MEMORY не подходит)

Кстати, примерно подходящий ответ я встречал на данном форуме когда-то давно (КАК НЕ ХВАТАЕТ ЗАКЛАДОК и ЛИЧНЫХ ЗАМЕТОК) (и номеров страниц тоже)

> КАК НЕ ХВАТАЕТ ЗАКЛАДОК и ЛИЧНЫХ ЗАМЕТОК
+1! Подписываюсь!!!
(сори за оффтоп)

Окститетесь :))), вон "Добавить закладку" и "мои закладки", хоть подразделы вводите, хоть название тем меняйте, если не нравится, хоть редактируйте...

Винда, падла, имеет свойство слетать в самый неожиданный момент, без возможности восстановления и без шанса сохранить закладки. Помните тему про вирус? А могло и не так все кончиться ) Даже когда переустановка плановая - что-нибудь да забудешь. Поэтому этой функцией пользуюсь исключительно кратковременно.... Чтоб не забыть с чем-нибудь разобраться. Кроме того закладки вечно разбросаны по браузерам, т.к. одним пользоваться не получается. Удаленно хранить - лично я бы пользовался )

Тыдышь!!!!
Блин. Ток ща аттач увидел. И ток щас обшарил страницу и разглядел эту ссылку! Оказывается есть )))) Бум пользоваться )))
А я подумал, что вы говорите про "добавить закладку" браузеров )

Одним запросом хотите решить задачу? Без вспомогательных таблиц?

>Одним запросом хотите решить задачу? Без вспомогательных таблиц?

Поскольку речь идёт о временной таблице с парой тысяч строк,
я собирался сделать 5 запросов к таблице рекурсивной функцией на PHP,
выбирая всех прародителей до 7-го колена,

а потом сделать выборку номера невесты, запросом с функцией уже на MySQL,
но не знаю даже... MySQL поддерживает-ли рекурсивные вызовы функций...

Кроме того, в какой форме передать функции массив аргументов, содержащий нежелательные номера?

Мне не обязательно, чтобы красиво, лишь бы работало.
Но вспомогательные таблицы нежелательны: я уже и так в этой базе намучился разбирая что когда и как локировать.
Кроме того, необходимо учитывать, что родительские поля могут быть неопределены(NULL)

Да, MySQL поддерживает рекурсивные вызовы... Можно потом просто создать еще одну таблицу, в которой для каждой строчки по cron-заданию вычисляются прародители до 7 колена, можно это делать один раз в сутки или час - будет некоторая задержка в актуальности, зато все эти рекурсивные задачи сменятся чрезвычайно быстрым поиском по такой промежуточной таблице.

>Да, MySQL поддерживает рекурсивные вызовы...
В смысле: не поддерживает?

Можно потом просто создать еще одну таблицу, в которой для каждой строчки по cron-заданию вычисляются прародители до 7 колена, можно это делать один раз в сутки или час - будет некоторая задержка в актуальности,

В данном конкретном случае задержка по актуальности категорически недопустима.
Так как она разрушает суть генетического алгоритма используемого в данном приложении.

Именно быстрое образование кортких близкородственных линий нарушает статистические критерии выявления наиболее благоприятного эволючионного пути. В данном случае, наоборот, таблицы придётся локировать, пока вопрос родственных связей не будет выяснен.

Я пробовал написать функцию сбора информации о родственниках отца.
Первая версия была рекурсивной - оказалось - MySQL не поддерживает рекурсивные функции.

Вторая версия выглядит вот так:

DROP FUNCTION `ansestors`//  FUNCTION `ansestors`(child int, parent1 int, parent2 int, maxAnsestorLevel int) RETURNS int(11)     COMMENT 'Собирает в таблицу fatherrelatives предков s из таблицы points.' BEGIN DECLARE p INT; DECLARE knee INT DEFAULT 0; DECLARE f INT DEFAULT NULL; DECLARE m INT DEFAULT NULL; DECLARE maxIterations INT DEFAULT 2; DECLARE iterations INT DEFAULT 0; SET knee = 0; DELETE FROM fatherrelatives; IF( child IS NOT NULL ) THEN     INSERT INTO fatherrelatives SET pointNum = child, knee = knee; ELSE RETURN 0; END IF; IF( parent1 IS NOT NULL ) THEN     INSERT INTO fatherrelatives SET pointNum = parent1, knee = knee + 1; END IF; IF( parent2 IS NOT NULL ) THEN     INSERT INTO fatherrelatives SET pointNum = parent2, knee = knee + 1; END IF; IF( parent1 IS NOT NULL OR  parent2 IS NOT NULL ) THEN     SET knee = knee + 1;     IF(knee > maxAnsestorLevel) THEN RETURN knee; END IF; ELSE RETURN knee; END IF; wet: WHILE knee < maxAnsestorLevel DO     SELECT pointNum, knee INTO p, knee  FROM fatherrelatives WHERE investigated = 'no' ORDER BY knee LIMIT 1;     IF( knee > maxAnsestorLevel) THEN         RETURN knee;     END IF;          SET iterations = iterations + 1;     IF(iterations > maxIterations ) THEN         RETURN knee;     END IF;              SELECT parent1, parent2 INTO f,  m  FROM points WHERE num = p;     IF( f IS NOT NULL ) THEN         INSERT INTO fatherrelatives SET pointNum = f, knee = knee + 1;     END IF;     IF( m IS NOT NULL ) THEN         INSERT INTO fatherrelatives SET pointNum = m, knee = knee + 1 ;     END IF;     UPDATE fatherrelatives SET investigated = 'yes' WHERE pointNum = p;      END WHILE wet; RETURN knee; END

maxIterations - Чтобы избежать зацикливания (на всякий случай)

Смысл в том, что после занесения известных родителей parent1 int, parent2 int,
ищем какие ещё из (ближайших - knee) родственников не исследованы (investigated) на предмет наличия предков и заносим их в таблицу, ставя флаг, что уже исследованы.

Но данная функция не работает,
а как её отлаживать непонятно.
Ошибок не выдаёт, но и родственников нормально не собирает.

>>Да, MySQL поддерживает рекурсивные вызовы...
>В смысле: не поддерживает?
Да от чего такое недоверие? MySQL опирается на уже существующие языки с поддержкой рекурсии и сама она тоже рекурсию поддерживает. Более того, это заложено в любой современный язык программирования, да и вообще любой язык программирования, даже просто язык, без программирования :))).

MySQL опирается на уже существующие языки с поддержкой рекурсии и сама она тоже рекурсию поддерживает.
Это было бы здорово, если бы поддерживалась рекурсия, но тогда в чём конкретно эта поддержка состоит?

Я только вчера получил заявление от MySQL прямым текстом, что рекурсивные вызовы функций недопустимы.
Вам удавалось скормить рекурсивную функцию MySQL?
Тогда очень хотелось бы увидеть этот поучительный пример.

Или речь идёт о чём-то другом?

Функции может быть (что-то да, там такое было), процедуры точно должны поддерживать рекурсивный вызов...

>Функции может быть (что-то да, там такое было), процедуры точно должны поддерживать рекурсивный вызов...

А смысл какой?
Если нельзя через возвращаемый аргумент передать то, что насобирали потомки,
то всё надо ссыпать в одну кучку (табличку) - это и без рекурсивного вызова ...

Вообще, что такое рекурсивный вызов процедуры?
В книге вы об этом не писали.
Рекурсивные функции - это общеизвестно, а вот процедуры... да ещё рекурсивные... я просто теряюсь...

Давно-давно когда-то встречал бейсик с процедурами, но тогда у меня ещё соска изо рта выпала и я отвлёкся не успев разобраться что к чему : )

Зависит от книги, но вроде везде старались упоминать. В MySQL два вида (внешне больше, но по внутренней сути два) организации кода:
CREATE FUNCTION - хранимая функция (вызывается как обычная функция)
CREATE PROCEDURE - хранимая процедура (вызывается ключевым словом CALL)
Функция возвращает значение, как традиционная C-функция, в C-подобных языках нет конструкции "процедура", однако, она есть в SQL, более того, много лет назад, когда я знакомился с диалектом SQL в MySQL я дивился, что у них оказывается еще и хранимые функции есть, странно, вроде по стандарту должны быть только процедуры, но стандарты SQL мало где реализованы точно, поэтому заморачиваться не стал. Ситуация мне была знакома, процедуры я знал по Fortran, а функции по C. Если, для вас это в новинку, пожалуйста, обратите внимания, это разные конструкции одного и того же. У них свои собственные заморочки. Функции они вроде как удобнее, особенно для программистов, знакомых с C-подобным языком, но вообще по стандарту SQL положены процедуры - поэтому любая уважающая себя СУБД их реализует.

Благодарю за столь подробный ответ!
Но он не на мой вопрос.
Про процедуры я в вашей книге давно прочитал, но там нет ни одного примера рекурсивного вызова процедуры поэтому я просто не понимаю этого словосочетания.
РЕКУРСИВНЫЕ ПРОЦЕДУРЫ - что подразумевается - ну хоть простейший примерчик!

Книг у нас много, вот в "MySQL: на примерах" я вижу целый подраздел им посвящен, я не очень понимаю, что вызывает сложность, просто вместо хранимый функции (там действительно есть ограничения) создайте хранимую процедуру - их точно можно делать рекурсивными, передавая нужные значения через параметры. Вот допустим рекурсивная процедура удаления каталога (для упрощения без удаления товарных позиций)

CREATE PROCEDURE delcat (IN id INT) BEGIN   DECLARE is_end INT DEFAULT 0;   DECLARE id_cat INT DEFAULT 0;   DECLARE cat CURSOR FOR    SELECT id_catalog FROM catalogs WHERE id_parent = id;   DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_end = 1;   OPEN cat;   wet : LOOP     FETCH cat INTO id_cat;     IF id_cat > 0 THEN CALL delcat(id_cat);     END IF;     DELETE FROM catalogs WHERE id_catalog = id_cat;     IF is_end THEN LEAVE wet;     END IF;   END LOOP wet;   CLOSE cat;   DELETE FROM catalogs WHERE id_catalog = id; END //

А вот это уже интересно!
Я такого не читал. У меня книга называется просто (MySQL 5).
Спасибо! Буду разбираться

Кстати, возможно вам будет интересно почитать и MySQL на примерах, там много, того, что нет в MySQL 5 (писали позже по уже более свежей версии).

>Кстати, возможно вам будет интересно почитать и MySQL на примерах, там много, того, что нет в MySQL 5 (писали позже по уже более свежей версии).

Да, интересно было бы посмотреть на примеры с курсорами итд.

Но данная тема осталась незакрытой.

Я написал рекурсивную процедуру собирающую всех предков до 7-го колена в простую таблицу.
Действительно, рекурсивные вызовы можно осуществлять!

Но это меньшая часть проблемы: можно было и без рекурсии написать, хотя так код более длинный вышел бы, и, соответственно, дебажить дольше.

Как теперь сделать выборку невесты у которой нет родственников входящих в этот список.

Или выборку с сортировкой по длине родственной связи.

СALL getParentsAndSelf(id, maxKnee);

Возвращает 4 колонки
1 первичный ключ
2 id
3 parent
4 колено - ну, не совсем колено, но на какой ступени пра пра пра находится родственник .(0) - для id=parent

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

Кажется, сообразил примерно во такой запрос надо:

SELECT point FROM points WHERE NOT EXISTS        (       SELECT point WHERE        getParentsAndSelf(point, knee)   IN(Список родителей жениха,включая самого жениха)       )

Только надо переделать getParentsAndSelf() так, чтобы она возвращала одну колонку родственников.

Если синтаксис вызова процедуры правильный, то, думаю - зто мне и нужно.

Взгляните, пожалуйста, на предмет: правильно ли я вызываю процедуру?
Как их вообще вставляют в запросы?

>Одним запросом хотите решить задачу? Без вспомогательных таблиц?
Допустим, я заполню временную таблицу родственниками жениха,
но как при выборке невесты соотносить её дальних родственников с этой таблицей, я пока сообразить не могу.