Рекурсивные (Иерархические) запросы

Дерево в БД можно представить достаточно просто:

ID | PID | NAME| VALUE

Куда сложней из такой таблицы вытаскивать данные. Можно это делать рекурсивно выполняя маленькие запросы, узнающие значение и родителя.
Оказывается эту задачу можно решить одним запросом используя лишь один T-SQL.
Рассмотрим на примере: необходимо выбрать все ветки/листья элемента, вывести значения элементов и построить полный путь до каждого.
Для этого можно использовать следующий запрос:

WITH tree ( data , id , pid , level , pathstr ) 
AS ( SELECT t.NAME, t.ID, t.PID, 0, CAST ( t.VALUE AS VARCHAR ( 4000 ) ) 
FROM table t
WHERE t.ID = 1 
UNION ALL 
SELECT t.NAME, t.ID, t.PID, t.level + 1 , CAST ( tr.pathstr + '\' + t.VALUE) AS VARCHAR ( 4000 ) ) 
FROM table t
INNER JOIN tree tr 
ON ( t.PID = tr.id ) 
) 
SELECT tr.* 
FROM tree tr 
ORDER BY tr.level, tr.pathstr 

Что делает каждая его часть:

WITH tree ( <список столбцов для выборки из дерева > ) 
AS ( 
<Начальное условие, откуда начнется рекурсия> 
Изначально путь = NAME корня, а уровень задан в 0
Строку с путем кастим к максимальному VARCHAR, т.к. путь может быть очень длинным.
UNION ALL 
<связка текущего запроса с деревом (родитель с идентификатором)>
Тут же мы увеличиваем уровень вложенности, и формируем следующий элемент пути
) 
<Тут мы работаем с полученными значениями, как с обычной таблицей>

Результат будет примерно такой:

id | pid | level | data                | pathstr
1  |  0  |   0   | Значение Корня      | Корень 
2  |  1  |   1   | Значение уровня 1   | Корень / Уровень 1
3  |  1  |   1   | Значение уровня 1.1 | Корень / Уровень 1.1
4  |  2  |   2   | Значение уровня 2   | Корень / Уровень 1 / Уровень 2

Вывод результата выборки в строчку: FOR XML PATH

Бывает необходимость вывести результат выборки в строчку.
Рассмотрим на примере. Есть задача и список исполнителей, необходимо построчно вывести задачи и ответственных. В случае группировки по задаче, ответственному мы получим число записей = число задач * число ответственных, нам же требуется вывести задачи построчно. Это отлично реализуется с помощью инструкции MS SQL - FOR XML PATH, изначально используемой для формирования xml строки из выборки.

Пример кода: task - Задачи, executer - Исполнитель:

SELECT
e.name, -- название задачи
(SELECT t.name + ', ' -- список исполнителей для задачи через запятую
 FROM task t
 WHERE t.executor = e.id
 FOR XML PATH(''))
FROM executer e

Рассмотрим более интересный пример, более близкий к изначальному применению FOR XML PATH - сформируем CSV файл из таблицы одним запросом без всяких циклов:

SELECT
t.field1 + ';' + --объединяем список столбцов через разделитель
t.field2 + ';' +
t.field3 + ';' +
t.field4 + ';
' --перенос строки
FROM table t WHERE 1=1 FOR XML PATH('')

Если field не строка, то его нужно привести: CAST(field AS VARCHAR(50)).

Добавление для ORACLE

SELECT t.executor, wmsys.wm_concat(t.name) 
FROM FROM task t 
GROUP BY t.executor

Векторный графический редактор в браузере на Raphael

SVG во всю набирает обороты и уже скоро сместит с пьедестала Flash. Не буду рассказывать о преимуществах, о этом можно прочитать в википедии.
На текущий момент у SVG есть один недостаток - нет поддержки в IE. У Microsoft собственный стандарт для векторной графики в браузере - VML.
Для решения этой проблемы разработана специальная библиотека Raphael, предоставляющая общий API для построения векторной графики, не зависимо от браузера.

В свою очередь я хочу представить графический редактор для построения векторной графики на основе RaphaelJS и чистоного SVG/VML.

Пример работы:

Opera (Firefox, Chrome, Safari - аналогично)	Internet Explorer
Трансформация, поворот, масштабирование, закругление:

Попробовать демо редактора

Для просмотра списка возможностей читайте далее.

Исправление ошибок в модуле связи Punbb и Drupal

Исправил следующие ошибки в модуле:
1. Создание пользователя форума с пустым паролем.
2. Ошибка авторизации при привязке cookie к домену (проявлялось на некоторых браузерах).
3. Установка языка форума для пользователя, основываясь на глобальных настройках.
Спасибо golden[a]speechpro.com (Golden) за тестирование.

Ссылка для скачивания прежняя.

NOT IN в MS SQL

В продолжение топика по оптимизации подзапросов в MS SQL, хотел бы сказать еще по поводу использования NOT IN. Точней его не использовании, т.к. в противном случае не подтягивается индекс и происходит полный перебор. Если есть возможность замените на эквивалентный запрос или перечисление констант.
Было:

...
field NOT IN (value1, value 2)
...

Стало:

...
field <> value1 AND field2 <> value2
...

Модуль связи PunBB 1.3 и Drupal 6

Модуль связи PunBB и Drupal
Изменения:
1. Переработка модуля для версии Drupal 6
2. Усложнение авторизации и регистрации форума punbb привело к изменению модуля.

Напомню еще раз характеристики:
Только через Drupal. Рекомендую на пунбб вообще отключить регистрацию.
1. Единая регистрация
2. Единая авторизация
3. Единый выход
4. Единая смена учетных данных (В данной версии: почта, временная зона и пароль)
5. Анонс нодов на форуме

Доступ внутри node к информации о топике:

$node['punbb'] == array('id', //ид топика
                        'subject', //тема топика
                        'posted', //автор топика
                        'url'); //url до топика на форуме

Дата разработки: ноябрь 2009
Основное место обсуждения

Скачать архив (Размер файла: 8КБ)

Синтез виртуальной среды с применением аналитических и скалярных функций возмущения

Ранее я выкладывал исходный код своего диплом (Синтез виртуальной среды с применением скалярных и аналитических функций возмущения и трехмерных массивов вокселей). Думаю многих интересуют детали реализации, для этого я выложу полную записку в общий доступ.

Оглавление:

Оглавление 6
Введение 9
1. Постановка задачи 11
1.1. Основные понятия и определения 11
1.2. Общее описание программной системы 11
2. Анализ методов и средств решения поставленной задачи 15
2.1. Теоретически основы 15
2.1.1. Физика процесса распространения света 15
2.1.2. Основная модель трассировки лучей 22
2.1.3. Способы задания свободных форм объектов 25
2.1.4. Функция возмущения в неявном виде 26
2.1.5. Функция возмущения в скалярном виде 28
2.1.6. Моделирование текстуры 28
2.1.7. Рельефное текстурирование 29
2.2. Аналитический обзор существующих систем синтеза воксельной графики 30
3. Анализ требований к программной системе 33
3.1. Анализ предметной области 33
3.2. Определение функциональности программной системы 34
3.3. Система приоритетов при разработке программной системы 34
4. Проектирование программной системы 36
4.1. Архитектура программной системы 36
4.2. Выбор инструментальных средств разработки 37
4.2.1. Выбор языка программирования 37
4.2.2. Выбор среды реализации 37
4.2.3. Выбор компилятора 38
4.2.4. Выбор прочих средств разработки 38
4.3. Проектирование структур данных, иерархии классов и алгоритмов 38
4.3.1. Описание структур данных 39
4.3.2. Описание алгоритмов программы 44
4.3.3. Оценка скорости работы алгоритма трассировки лучей 47
4.4. Проектирование пользовательского интерфейса 49
5. Реализация программной системы 50
5.1. Особенности реализации системы 50
5.1.1. Оценка объема исходного кода 54
5.2. Внешний вид сцен 54
6. Тестирование программной системы 59
6.1. Обоснование методики тестирования 59
6.2. Результаты тестирования 59
7. Внедрение системы 65
7.1. Руководство пользователя 65
7.2. Руководство программиста 66
8. Организационно-экономическая часть 68
8.1. Анализ и оценка стоимости объекта интеллектуальной собственности 68
8.1.1. Анализ объекта интеллектуальной собственности 68
8.1.2. Подходы к оценке стоимости объекта интеллектуальной собственности 70
8.1.3. Оценка стоимости объекта интеллектуальной собственности 75
8.2. Расчет показателей экономической эффективности 76
8.3. Выбор и обоснование методики ценообразования разработки 81
8.4. Способы коммерциализации объекта интеллектуальной собственности 83
9. Безопасность и экологичность проекта. 85
9.1. Анализ опасных и вредных факторов пользователей ПЭВМ 86
9.1.1. Опасные и вредные факторы при работе за мониторами. 88
9.2. Решения по охране труда 92
9.2.1. Проектирование естественного освещения 93
9.2.2. Проектирование искусственного освещения 96
9.3. Решения по обеспечению устойчивости функционирования в чрезвычайных ситуациях 99
9.3.1. Основные понятия чрезвычайных ситуаций 99
9.3.2. Информационная безопасность 101
Заключение 108
Список литературы 110
Приложение А 113
Листинг основных алгоритмов 113
Приложение Б 122
Организационно-экономическая часть 122
Приложение В 123
Безопасность и экологичность проекта 123
Приложение Г 124
Архитектура системы 124
Приложение Д 125
Общий алгоритм функционирования системы 125
Приложение Е 126
Тестирование системы 126
Приложение Ж 127
Алгоритм трассировки лучей 127

Далее примеры сцен и ссылка на скачку полного файла.

Продажа контента за SMS

Сейчас очень распространенным стала оплата услуг и товаров через СМС. Но не многие знают как это все организовано.
Я бы хотел рассказать об одном из простых способов.

Рассмотрим пример оплаты и прокомментируем каждый этап исходным кодом на PHP.

0. Размещению на сайте оплаты через смс предшествует заключение договора с компанией предоставляющей эти услуги.
1. Пользователь заходит на страничку, где ему предлагается скачать приложение, предварительно отослав смс с текстом на указанный номер.
Реализация: Выводится статичные текст с текстом и телефоном, который предоставила на предыдущем этапе организация
2. Клиент отправляет СМС. Ему приходит ответная с уникальным кодом на доступ
Реализация: Сервер смс компании, получает сообщение с нужным тектом на номер. Генерируется событие, вызывающее произвольную (настраиваемую) страницу на нашем сайте. На это странице мы произвольны сделать, что угодно.
Генерируем код, и записываем в БД:

function getCOde() {
  $code = sprintf("%06x", rand(1000000, 10000000)); //рандомное число, преобразованное в hex
  //проверяем на повторения
  $res = db_query("SELECT COUNT(*) AS cnt FROM {sms_pay} WHERE code = '%s'", $code);
  $codes = db_fetch_array($res);
  if($codes['cnt'] > 0) {
    getCode(); //генерируем еще раз
  }
  db_query("INSERT INTO {sms_pay} (code) VALUES('%s')", rand(), $code); //записываем код в БД
  return $code;
}

Сгенерированный код, вываливается через echo в браузер, а сервер весь сгенерированый текст отправляет в виде смс обратно клиенту.
На данном этапе код еще не привязан ни к одному материалу и его можно активировать на любом.
3. Клиент получил код, вводит его, чтобы получить доступ к материалу
Реализация: Сервер сайт получает код, проверяет его наличие в БД. Если код верный, то в БД вносятся данные о файле к которому был получен доступ, также для защиты от передачи кода третьим лицам в бд заносится ip адрес клиента и ставится кука.
Файл может быть скачан не сразу, а по частям. В этом случае пользователю дается несколько часов на скачку файла: в бд заносим также время первого обращения к файлу.
После этого пользователю файл стримится прямо в браузер с утановкой необходимых хеадреов, чтобы у клиента не было прямого доступа к файлу (Об этом можно почитать отдельно, я пользуюсь стандартными функциями Drupal)
4. Клиент повторно обращается к этому же файлу.
Реализация: При повторном обращении к файлу, снова проверять код не обязательно. Необходимо сверить лишь куки и ip пользователя, а также id файла к которому идет обращение.
5. Если пользователь пытается обратится к другому файлу с этим же кодом, то это ему не удастся, т.к. он не пройдет проверку по id файла.
6. Код был передан третьим лицам. Пользователь не пройдет проверку по ip и куке.
Примерный код для предыдущих пунктов можно видеть в листинге: (php/Drupal, точка входа функция sms_pay_file_download)

Что быстрей LEFT JOIN или подзапрос в SELECT

Часто требуется выбрать дополнительные параметры плюсом к основной выборке.
Это можно сделать двумя способами:

• LEFT JOIN к основному запросу
• Подзапрос в секции SELECT

Пример запроса, выбирающего номенклатуру и товарную группу номенклатуры:

Подзапрос в секции SELECT:

SELECT nom.Наименование,
(SELECT tg.Наименование FROM Товарная группа tg WHERE tg.ID = nom.Товарная группа)
FROM Номенклатура nom

Вариант с LEFT JOIN:

SELECT nom.Наименование, tg.Наименование
FROM Номенклатура nom
LEFT JOIN Товарная группа tg
 ON(tg.ID = nom.Товарная группа)

После разбора плана выполнения запроса в MS SQL Server, было выявлено:

• При выборке в секции SELECT требуется дополнительное время на слияние основной выборки и подзапроса. Вышло 1% от общего времени.(+ LEFT JOIN / - SELECT)
• Оказалось, что каждый LEFT JOIN выполняется отдельным потоком! В то время как подзапросы выполняются последовательно после основной выборки. (+ LEFT JOIN / - SELECT)
• Каждый LEFT JOIN объединяется в результирующую выборку, что требует дополнительной памяти. В то время как подзапрос вернет нам одно значение на каждую строку, т.е. для получения результата нам нужно меньше памяти. (- LEFT JOIN / + SELECT)

Вывод: В условиях многоядерных серверов LEFT JOIN имеет неоспоримые преимущества

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

SELECT nom.Наименование,
(SELECT TOP 1 tg.Наименование FROM Товарная группа tg WHERE tg.ID = nom.Товарная группа)
FROM Номенклатура nom

Такой запрос выполнялся дольше на 96% по сравнению с аналогичным без TOP 1

Разбор плана показал, что 1% дополнительного времени тратится вложенный цикл, а 95% на просмотр определенных строк не кластеризованного индекса!
Из этого можно сделать один вывод: никогда не используйте без надобности ограничения в подзапросах.

В заключении хотел бы сказать, что бывают ситуации когда без подзапросов не обойтись (группировки, сортировки и т.д.), но использовать их нужно обосновано, если есть возможность, то лучше пользоваться LEFT JOIN.

Установка Linux Fedora

Памятка по установке Linux Fedora 
1. Разбиение диска 
Я предпочитаю разбивать диск сам, не доверяя установочным скриптам
Необходимо создать 2 раздела:

• Linux Swap  - раздел Primary, размер равен двум объемам ОЗУ
• Linux Ext - раздел Primary, размер достаточный для установки ОС/Программ, не менее 4 Г.

2. Процесс установки

• Установка с DVD диска
- Указываем расположение свапа и основного раздела. Это не составит труда, т.к. диск уже разбит раньше
• Установка с жесткого диска
- Создаем загрузочный диск из папки isolinux
- Указываем расположение образа диска - Указываем расположение свапа и основного раздела. Это не составит труда, т.к. диск уже разбит раньше

3. Настройка загрузчика GRUB
grub.conf - загрузка винды по умолчанию через 5 секунд:

default=2
timeout=5
splashimage=(hd1,2)/boot/grub/splash.xpm.gz
hiddenmenu
title Fedora 7
 root (hd1,2)
 kernel /boot/vmlinuz-2.6.21-1.3194.fc7 ro root=LABEL=/ rhgb quiet
 initrd /boot/initrd-2.6.21-1.3194.fc7.img
title Windows
 rootnoverify (hd0,0)
 chainloader +1

4. Автомонтирование дисков
fstab - cdrom, fat/ntfs, дискета, usb

LABEL=/                 /                       ext3    defaults        1 1
tmpfs                   /dev/shm                tmpfs   defaults        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0
/dev/fd0                /mnt/a                  vfat    user,noauto,rw  0 0
/dev/sda5               /mnt/f                  vfat    defaults        0 0
/dev/sdb5               /mnt/d                  ntfs    force,rw        0 0
/dev/sdb1               /mnt/c                  ntfs    force,rw        0 0
/dev/sdc1               /mnt/usb                vfat    user,noauto,rw  0 0
LABEL=SWAP-sda1         swap                    swap    defaults        0 0

5. Установка репозитория

• Копируете все rpm пакеты с диска/дисков в отдельный катклог.
• Выполняете команду createrepo /путь/к/катклогу (если такой команды нету ищете на дисках пакет он так и называется createrepo)
  Заходите в /etc/yum.repos.d/ удаляете все файлы(копируете в другое место) и создаёте свой fedoracore.repo следующего содержания:
  

  [core]
  name=Fedora Core $releasever - $basearch
  baseurl=file:///путь/к/базовым_репозиториям
  enabled=1
  gpgcheck=0
  gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-fedora file:///etc/pki/rpm-gpg/RPM-GPG-KE

   
• Запускаем yum list, и если всё прошло благополучно, радуемся жизни 

6. Установка mc
http://mirror.yandex.ru/fedora/tigro/10/i386/
7. Установка mplayer и кодеков
Ставим репозиторий

rpm -Uvh http://download1.rpmfusion.org/free/fedora/rpmfusion-free-release-stable.noarch.rpm http://download1.rpmfusion.org/nonfree/fedora/rpmfusion-nonfree-release-stable.noarch.rpm

Ставим из него mplayer, vlc, xmms и т.д.
8. Установка драйверов видео
9. Установка wine
10. Установка другого ПО: Opera, Apache, PHP и т.д.

Движок воксельной трассировки лучей

Движок виртуализации трехмерной графики на основе трассировки лучей (фотореалистичная графика)
Возможности:
• Визуализация поверхностей второго порядка
• Виртуализация пространства на основе скалярных и аналитических функций возмущения над поверхностями второго порядка.
• Виртуализация пространства на основе трехмерных массивов вокселей
• Освещение сцены точечными, сферическими и коническими источниками света
• Возможность наложения как проективных текстур (из файла), так и программно-генерируемых
• Возможность наложения текстур изменяющих свойства материала и вектора нормали
• Возможность экспорта (растеризации) готовых сцен в популярные форматы (JPG, GIF, PNG, TIFF, TGA)
• Переносимость продукта на популярные платформы (Unix, Windows, Mac, ОС мобильных устройств и т.д.)
• Кластеризация расчетов (Разделение рендера на независимые потоки)
Характеристики: c++, qt, многопоточность, переносимость
Дата разработки: февраль-май 2009

Скачать архив (Размер файла: 2080 КБ)

Конструктор тестов с экпортом

Конструктор тестов/опросов/голосовании с возожностью экспорта на стороние сайты (самих тестов и статистики)
Характеристики: php, mysql, ajax, jsonp, jquery
Дата разработки: март-май 2009

Скачать архив (Размер файла: 59 КБ)

SimplePDO

Класс расширяющий и упрощающий работу с PDO
Синтаксис:

$r = $sp->query("insert into ?_url_alias(src, dst) VALUES(%s, %s)", "tst", "test");
echo $sp->query("update ?_url_alias set dst = %s where pid = %u", "tst1", $r);
print_r($sp->selectCol("{?_url_alias} select * from ?_url_alias"));
print_r($sp->selectRow("{?_url_alias} select * from ?_url_alias where pid = %u and dst = %s", 1, "news"));
print_r($sp->select("select * from ?_url_alias"));

Возможности: плэйсхолдеры, кэширование, перфиксы таблиц, выборка таблиц/строк/ячеек/столбцов
Дата разработки: июль 2008

Скачать архив (Размер файла: 4КБ)

Шаблонизатор SimpleTpl

Простой шаблонизатор.
Пример использования:

Read Template and set Variables
->getTpl(string Tpl [, mixed Variable1 [, mixed Variable2] [, ...]]);
return: String template with replace variables. Exit program, if template not exist

Дата разработки: июль 2008

Скачать архив (Размер файла: 915Б)

Модуль связи PunBB и Drupal

Модуль связи PunBB и Drupal
Только через Drupal. Рекомендую на пунбб вообще отключить регистрацию.
1. Единая регистрация
2. Единая авторизация
3. Единый выход
4. Единая смена учетных данных (В данной версии: почта, временная зона и пароль)
5. Анонс нодов на форуме
Дата разработки: февраль - апрель 2008
Основное место обсуждения

Скачать архив (Размер файла: 8КБ)

WikiMedia

Wiki движок
Особенность от обычного wiki: возможность создания виджетов (программ написанных на внутреннем языке движка (подмножестве PHP)), с целью автоматизации процессов внутри сайта
Основные виджеты: вставка аудио/видео, сжатие картинок, преобразование ссылок (виджеты можно создавать самому)
Характеристики: php/mysql/mod_rewrite
Дата разработки: октябрь-декабрь 2007

Скачать архив (размер файла: 3221КБ, внутри есть полное описание проекта)

PHP-CRAFT

Портальная система для создания сайтов:
Харктеристики: PHP/MYSQL/MOD_REWRITE/модульность/блочность/смена тем/wysiwig
Дата создания: 2004-2006 года

Скачать файл (Размер архива: 1043КБ)