Из серии бесполезных фактов

Только что опытным путем было доказано, что в оракле работает такая конструкция

update table set value1 = rownum where id >3

Только непонятно как оно сортирует строки.

Вести с полей

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

Это был один из самых сложных моих проектов (работали по 10-12 часов в сутки) и я рад что я его сделал.

У Тома Кайта есть известная фраза по поводу написаний приложений под Оракл: "... все что можно я делаю средствами SQL, если задачу нельзя решить средствами SQL я использую PL/SQL, если задачу нельзя решить средствами PL/SQL я использую Java, если и средств Java недостаточно, то я использую Pro*C ...".

Так вот во время написания проекта я дошел до стадии "нельзя решить средствами PL/SQL" - интересно, когда нибудь, я дойду до стадии "если и средств Java недостаточно".

Вообщем я горд и доволен собой.

А еще у меня скоро день рождения и я собираюсь подарить себе такую штуку

Сортируем как хотим

Сегодня, экспериментальным путем, выяснилось, что decode можно ставить даже в order by.
Т. е. запрос вида

select * from tbl1 order by decode(:order_param, 1, param1, 2, param2, param3)

будет работать. Причем на "стоимость" запроса это не повлияет так как сортировка делается после выборки данных.

К сожалению используя этот способ нельзя менять направление сортировки с asc на desc. Но в таком случае может помочь аналитическая функция row_number()

select * from
(select param1, param2, param3, row_number() over (order by decode(:param_num, 1, param1, 2, param2, 3, param3)) rn from tbl1)
order by decode(:order_destination, 'desc', 1/rn, rn)

В этом примере, с помощю функции row_number() мы присваевам каждой строке номер который она получила бы в отсортированном массиве. А в обрамляющем запросе мы или просто сортируем запрос по этому столбцу чтобы получить прямую сортировку, или сортируем по 1/rn и получаем обратную сортировку

Внимание: второй способ я еще не проверял так что он может быть невполне работоспособен, хотя в самом принципе я уверен

P.S. наконец набрал в гугле sql syntax highliter и нашел подсветку для запросов, теперь форматирование запросов будет красивое и одинаковое :)

ER 2.0

Визуализатор баз данных онлайн. Правда заточен под MySql, но все равно интересно.

Теперь и тут

glebreutov.habrahabr.ru

Сумасшедшие идеи

У меня в телефоне, в записной книжке, есть файлик "Сумасшедшие идеи" куда я записываю все что прийдет в голову (за исключением того что даже записать лень). После записи мысли забываются и освобождают место для новых.

Вот небольшая выборка, тех записией которые показались мне интересными после повторного прочтения:

1. Чайник который сам генерирует воду электролизом или еще каким то зом. Чтобы вода не была дистиллированной предусмотрен специальный минеральный картридж.

2. Приложение, через которое можно было бы смотреть любую документацию в нормальном виде. Привет, Оракл! Про опенсорс вообще молчу

3. Метод работы со слоями при создании интерфейсов - многогранник на каждой грани которого располагается определенный слой

4. В SQL редакторе - кнопка save as view

5. В мессенджерах - возможность отправить голосовое сообщение или видеособщение.

Апдейт пословицы

Из SEO в СEO

Невероятные совпадения

Читай между строк табов:

Эксперементы с Маком 2

В Википедии я прочитал что tcl/tk (его почему то по русски его назвают тактиль) как то особо хорошо интегрируется с SQLite и решил его использовать (думал задача стоит быстро написать прогу, а не Питон выучить). Это оказалось ошибкой: в чем его особая интеграция с SQLite я так и не узнал, а повозится мне с ним пришлось.

SQLite - встраеваемая реляционная база данных. Простая и симпатичная. Единственное что в ней напрягает, то что она позволяет в поля любых типов записывать данные любых типов, а сами типы полей - просто подсказки, чтобы не забыть какие данные ты собирался там хранить изначально. Но тем не менее данные хранятся в таблицах выбираются селектом, а вставляются инсертом, а для настольных приложений больше обычно и не надо.

Вообщем тактиль оказался достаточно простым скриптовым языком, инфы по нему я нашел очень мало, а на превращение скриптов в бинарник ушло больше времени, чем на написание программы. Превращение скриптов в бинарник достаточно нетривиальное. оно основано на загонянии скриптов и интерпретатора в один файл, который представляет из себя виртуальную файловую систему VFS. Для того, чтобы создать бинарник нужно скачать или собрать tclkit (интерпретатор в одном файле), из скриптов программы собрать starkit, а потом все это собрать в starpack.

Кстати, неоценимую помощь в собирании программы в бинарник оказла програмка Starpacker, которая очень облегчила этот процесс.

Для себя я определил отношение к тактилю так: пользоватся можно, но что-то совсем не хочется.

Ну и собственно что у меня получилось. Программа отображает статус билета (билет валиден, билет невлиден, билет валиден но уже погашен) в виде дорожных знаков и ведет статистику. Программа прошла боевое крещение на новый год, в МВЦ


Эксперимент с Маком закончен, дальше будет эксперимент с Кокой (тоже на Маке)

Эксперименты с Маком :)

Недавно мне предложили написать под Мак несколько десктопных приложений. Взяться за это было достаточно рисковано, потому что настольные программки я последний раз писал на Делфи 6, на первом курсе, но любопытство победило.

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

Кстати, оказывается считыватель штрих кодов определяется как юсб клавиатура. При считывании штрих кода он делает вид, что нажимает на кнопки. За счет этого обеспечивается кроссплатформеность и отпадает нужда возится со считыванием битов, через юсб. По моему очень остроумное решнеие.

После этого начались муки выбора технологии на которой писать, несмотря на то что задача стояла простая - сроки поджимали. Требования были простые: чтобы, во первых, работало на Леопарде, во вторых на ХР. Из-за сроков я сразу отбросил такие емкие технологии как Java. Решил взять что-то простое. Выбор был между Python и Tcl/Tk. Python мне всем нрависля и я уже почти выбрал его, как ...

(продолжение следует)

Писюк под лупой Мака

Если на маке посмотреть сетевой компьютер с помощью быстрого просмотра, то он отобразится так



Для тех кто не понял что к чему - посмотрите внимательно на экран монитора.

MIT в сети

MIT (тот самый университет, в котором учился Гордон Фримен =) выложили в свободный доступ свои учебные материалы (http://ocw.mit.edu/). Просто кладезь мудрости. Правда открыли они эти сайты не вчера, но узнал я про их существование только сегодня.

А еще есть отличный эпловский образовательный ресурс iTunes U

К сожалению у нас таких ресурсов нет и скорее всего не появятся в ближайшее время.

UPD: открылся YouTube EDU

Оракле в Мозиле

Поисковый аддон для мозилы, который я юзаю практически каждый день. Ищет по документации Oracle 10g. Очень удобно.

Параметризованый SQL: способы параметризации IN

Первый раз проблема параметризации IN встала передо мной сразу после того как я узнал про связываемые переменные. Тогда я проигнорировал проблему, но сейчас решил к ней вернуться.

Первый способ. Декларативный.
Состоит в том чтобы объявить переменные для максимального количества значений. Поскольку он очевиден, то думаю что подробнее на этом способе останавливаться не стоит.

SELECT * FROM TABLE1
WHERE FK_ID IN (:IN_P1, :IN_P2, :IN_P3, :IN_P4, :IN_P5);

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

INSTR - функция возвращает позицию подстроки внутри строки

REGEXP_SUBSTR - возвращает подстроку соответствующую регулярному выражению. Функция regexp_substr появилась только в десятой версии оракла, в предыдущих нужно шаманить с функциями SUBSTR и INSTR.

Что такое иерархические запросы можно прочитать на хабре (на русском) и в документации (на английском)

Запрос принимает входящий параметр - строку со значениями разделенными запятой (или любым другим подходящим символом) и превращает его во вьюху состоящую из значений входящей строки. Из этой вьюхи берутся данные для оператора IN.
На практике это может выглядеть так:

SELECT * FROM TABLE1
WHERE FK_ID IN (select regexp_substr(:in_str, '[^,]+',1,level) FROM DUAL CONNECT BY INSTR(:in_str,',',1, level)>0);

Строка :in_str обязательно должна заканчиваться запятой, иначе последний параметр в строке будет пропущен.

Сравнение двух способов
С точки зрения удобства первый способ менее удобен чем второй - это очевидно, а с точки оптимизатора запросов есть один ньюанс. При использовании декларативного способа оптимизатор точно знает сколько значений находится в IN и может раскрыть скобки: ... id in (:p1, :p2, :p3) → id=:p1 or id=:p2 or id=:p3, а в случае с рекурсивным способом оптимизатор должен создать виртуальную таблицу и сделать inner join c основной таблицей. Стоит это учитывать.

Сохрнение документов

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

Пост в ЖЖ, о том что появилось поколение пользователей, которые никогда не видели дискету и пиктограмма с дискетой для них не очевидна.

Любой документ должен быть сохранен всегда, включая достаточное количество предыдущих версий (для обеспечения работы Ctrl+Z). Раньше это было невозможно из-за ограниченного дискового пространства и маленькой вычислительной мощности компьютеров, но сейчас отношение размера офисного документа к общему объему жесткого диска стремится к нулю, а вычислительные мощности избыточны. Правда при таком подходе возникает проблема: документ должен быть сохранен где-то и это где-то нужно указать. От этой проблемы легко избавиться если выбирать место для сохранения при загрузке, плюс место по умолчанию.

Введение в иерархические запросы

Хорошее введение в иерархические (рекурсивные) запросы http://habrahabr.ru/blogs/sql/43955

Механизм навигации по истории браузера

У меня в голове накопилось некоторое количество идей, которые я хочу опубликовать. Начну с этой. Здесь и в будущем посты будут отмечены тегом идея.

История браузера отображается в виде шкалы, отображающей историю в виде линейки - минимальным делением которой является переход с одной страницы на другую, а максимальным - переход с одного сайта на другой. По аналогии с традиционной линейкой - переход с одной страницы на другую - миллиметровое деление, а переход на другой сайт отображается как сантиметровое деление, с той разницей что на традиционной линейке все сантиметровые деления одинаковые, а в данном случае размер одного деления зависит от того сколько страниц на сайте посетил пользователь. Деления, которые обозначают переход на другой сайт - обозначаются логотипом сайта (если он есть) сверху и адресом сайта снизу. Кроме делений на шкале присутствует начальная точка - страница на которой сейчас находится пользователь.



Навигация по шкале истории осуществляется с помощью ползунка, при перемещении которого изменяется адрес и название страницы, а также отображается содержимое страницы (содержимое отображается моментально, при помощи заранее заготовленного скриншота страницы).


Принцип заполнения истории тот-же что и при традиционных кнопках навигации.

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

Так-же, подобная шкала может быть успешно применена к текстовым и графическим редакторам, дополняя кнопки undo и redo

Microsoft Security Development Lifecycle

Security Development Lifecycle - методика безопасной разработки от майкрософт. Сам еще не прочитал, но заинтригован.

Тут можно прочитать интервью с майкросовтофским разработчиком про SDL, а тут можно скачать собственно описание технологии (англ.)

Реализация фильтра типа "Не фильтровать ничего либо фильтровать по значению" на SQL, без преобразований запроса

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

При написании запросов для статистик (и не только) часто возникает необходимость сделать необязательный фильтр по значению, например фильтр по типу контента, в котором можно выбрать варианты: Все типы, Музыка, Видео, Картинка.
Обычно или пишут два разных запроса, которые отличаются только наличием фильтра (например: ...where type_id=:type_id) или, что бывает гораздо чаще, условие-фильтр хранят в отдельной переменной и в зависимости от входящих данных подставляют его в строку запроса или не подставляют
например (if type_id is not null then filter:='type_id=:type_id' else filter:='' endif;)

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

Можно легко избавится от этих недостатков, используя функцию decode или конструкцию case when .... then ... else .... end. По сути decode(a, b, c, d) это функция, реализующая конструкцию if a=b then c else d end if; Подробнее, о ней можно прочитать в документации. У функции есть два серьезных ограничения - она умеет работать только с числовыми типами данных (NUMBER, BINARY_FLOAT, или BINARY_DOUBLE) и в ней не работает сравнение с null.

Идея очень проста: проверять переменную на null прямо внутри запроса, с помощью такого кода ...where decode(nvl(:type_id, 0),0,0,type_id)=nvl(:type_id, 0). Если перевести этот код на человеческий язык, то получится: если переменная равна null (или 0) тогда сравниваем ее саму с собой, в противном случае сравниваем ее с полем, по которому предусмотрет фильтр. В первом случае, для всех строк получится выражение 0=0 - всегда истина, фильт не применится, а во втором type_id = :type_id выдаст только те строки, где это условие выполняется. Функция nvl используется для обхода ограничения decode, которое состоит в том что в ней не работает сравнение с null.


with sample_table as (
select 1 id, 1 type_id from dual union all
select 2 id, 1 type_id from dual union all
select 3 id, 1 type_id from dual union all
select 4 id, 2 type_id from dual union all
select 5 id, 2 type_id from dual union all
select 6 id, 2 type_id from dual union all
select 7 id, 3 type_id from dual union all
select 8 id, 3 type_id from dual union all
select 9 id, 3 type_id from dual
)
select * from sample_table where decode(nvl(:type_id, 0),0,0,type_id)=nvl(:type_id, 0);

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

Любые вопросы, мнения, эмоции или угрозы вы можете оставить в комментариях

В каких случаях использовать и в каких не использовать этот прием

В некоторых случаях этот прием может значительно повлиять на план выполнения запроса и как следствие на производительность самого запроса.
Приведу примеры, когда фильтр нельзя применять и когда можно. Создадим большую таблицу BIG_TABLE

CREATE TABLE BIG_TABLE as SELECT * FROM ALL_OBJECTS;

И создадим по ней индекс:

CREATE INDEX BIG_TABLE_INDEX1 ON BIG_TABLE ("OWNER", "OBJECT_NAME") ;

Пример когда нельзя использовать фильтр decode.

Задача: Если указан параметр :owner, то выводить объекты данног пользователя, в обратном случае выводить все объекты

.Напишем три запроса. Первый не будет фильтровать ничего второй будет фильтровать по полю owner, а третий будет основаный на вышеприведенном способе, будет делать или первое или второе.

1) SELECT * FROM BIG_TABLE;

В этом запросе индекс использоватся не будет так как нужны данные всей таблицы (в такой ситуации использование индекса неэффективно).

2) SELECT * FROM BIG_TABLE where owner=:owner;

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

3) SELECT * FROM BIG_TABLE where decode(:owner, null, 0,owner)=nvl(:owner,0);

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

Пример когда можно использовать decode фильтр

Задача: для объектов определенного пользователя возвращать список объектов определенного типа, если он указан. В противном случае выводить объекты всех типов.

Модифицируем запросы первого примера:

1) SELECT * FROM BIG_TABLE where owner=:owner;



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

2) SELECT * FROM BIG_TABLE where owner=:owner and objet_type=:obj_type;

Обратите внимание что условие objet_type=:obj_type стоит не в пути доступа а в условиях фильтра. Это значит, что запрос вначале выбирает строки соответствующие условию owner=:owner используя индекс, а потом фильтрует их по условию objet_type=:obj_type

3) SELECT * FROM BIG_TABLE where decode(:obj_type, null, 0,objet_type)=nvl(:objet_type,0);


Этот план практически не отличается от предидущего, за исключением того что в условиях фильтра стоит условие decode(:obj_type, null, 0,objet_type)=nvl(:objet_type,0). В этом случае, запрос 2 и запрос 3 практически не отличаются, поэтому можно смело использовать decode.

Другие случаи, когда не стоит использовать этот фильтр.
Поскольку оптимизатор будет принимать во внимание максимальное количество строк, возвращенное подзапросом, то это может повлиять на порядок и способ соединения таблиц.

Вывод: фильтр decode можно использовать в случаях когда условия фильтрации не является путем доступа, а является фильтром.