Oracle: быстрая вставка данных в таблицу

Уменьшение времени пакетной (для olap/dwh) вставки данных:
Отличительная особенность olap: вставка одна, но очень большая.

1. Делаем таблицу не логируемой.
Что уменьшит затраты на вставку в redo log.

ALTER TABLE T NOLOGGING

* Может не сработать, если в базе включено FORCE_LOGGING = YES

2. Добавляем /*+ append */ в insert операцию
* Данные добавляются в конец таблицы, вместо попытки поиска пустых мест.
* Данные пишутся напрямую в data файлы, минуя буферный кэш.

Стоит заметить один нюанс при вставке с хинтом append из разных сессий в одну таблицу. Так делать нельзя, т.к. direct path вставка блокирует все остальные сессий к этой таблице: http://docs.oracle.com/cd/B19306_01/server.102/b14231/tables.htm#sthref2260 . Только одна сессия может одновременно осуществлять direct path вставку в одну таблицу. Т.к. чтобы обойти буферный кэш, сначала нужно скинуть все грязные данные из кэша на диск.

3. Отключаем constraint, trigger на таблице и явно вставляем значения в default колонки.
Замечу, что если надо ускорить вставку, то надо отключать FK на самой таблице, а если удаление, то FK на других таблицах, которые указывают на нашу.

4. Распараллеливаем запрос хинтом /*+ PARALLEL (8) */
Не забываем включать параллельность для DML, чтобы параллелился и insert, а не только select.

ALTER SESSION ENABLE PARALLEL DML;

5. Если распаралеллить вставку нельзя, к примеру из-за доступа по dblink.
Можно физически распаралелить вставку через несколько одновременных вставок кусками части данных из источника.
Сделать это можно через dbms_parallel.
Очень хорошо подходит для одновременного копирования нескольких таблиц или если таблица партиционирована.
При вставке в одну таблицу незабываем про ограничения хинта append из п.2

6. Удаляем index и foreign key с внешних таблиц.
Пришлось именно удалять, т.к.
* DISABLE можно делать только у функциональных индексов
* UNUSABLE можно сделать на всех индексах, но DML запросы все равно будут валиться на UNIQUE index
http://docs.oracle.com/cd/B13789_01/server.101/b10755/initparams197.htm
Ничего страшного в этом нет, восстановление индексов заняло 5 минут по 10 млн записей, что все равно лучше 4 часов вставки.
Удаляем все, включая Prmary Key. Но тут не забываем, что каскадно удалятся и все FK. Их надо будет потом восстановить, ну или PK придется пожертвовать и оставить.

ALTER TABLE T DROP CONSTRAINT PK CASCADE

7. Делаем кэшируемым Sequence.
Если в insert используется sequence, то делаем его кэшируемым.
С "CACHE 50000" мне удалось сократить время вставки 10 млн записей с 50 минут до 5. Это в 10 раз!
При кэширумом sequence последовательность заранее подготавливает числа и хранит в памяти, а это значит, что накладных расходов обмена становится меньше.

8. IOT таблица
Если на таблице один индекс, который покрывает большую часть столбцов, то ее можно конвертировать в IOT таблицу. Так мы уменьшаем число обслуживаемых объектов до 1. Что уменьшает число буферных чтений с 3 (2 чтения индекса + 1 чтения таблицы) при любых DML/select до 2 (2 чтения индекса).

Уменьшение времени распределенной/многопользовательской (oltp) вставки данных:
отличительной особенности вставок в oltp является то, что их очень много, каждая из них создает микроскопическую нагрузку, но все вместе могут создать большое кол-во событий ожиданий (busy wait). Рассмотрим отдельно как обойти эти ожидания:

1. увеличение числа списка свободных блоков (free_list при создании таблицы)
 + уменьшение конкуренции за поиск свободных блоков за счет распараллеливания вставки
 - раздувание таблицы, т.к. когда заканчивается free_list1, то он не будет использовать свободные блоки из free_list2, а выделит новые поверх HWM
 - увеличивает фактор кластеризации индексов, т.к. данные физически раскидываются по разным местам таблицы, а не идут последовательно

2. сделать индекс реверсивным, если нет возможности отключить при вставке
 + уменьшение конкуренции за вставку данных в индекс, т.к. последовательные реверсивные данные будут использовать разные блоки индекса
 - увеличение фактора кластеризации из-за разброса данных
 - нельзя будет использовать range scan (сканирование по диапазону) индекса, т.к. в индексе уже не сами данные, а их инвертированные значения
Стоит заметить о факторе класетризации: чаще всего в oltp системе он не очень важен, т.к. доступ к данным идет по конкретному значению к одному конкретному блоку. Т.е. здесь нет скачков по разным блокам, как при сканировании по диапазону.

3. использование хинта append_values
 + запись данных не будет использовать free_list, а будет просто писаться поверх HWM
 - разрастание таблицы

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

5. Выполнение вставки используя prepared statement
что позволит исключить парсинг SQL перед его выполнением.

6. Вставка строк блоками (executeBatch)
Что позволит снизить задержки на network lookup - время на установку соединения и передачу данных по сети.

7. 7п. из пакетной вставки - кэшируемый индекс

8. остальные способы из пакетной вставки, если они применимы в текущей ситуации


Если знаете еще способы ускорить insert - пишите в комментариях.

В продолжении: быстрая вставка данных в партиционированные таблицы http://blog.skahin.ru/2015/06/oracle.html

Домашняя бухгалтерия на PHP 5, SQLite 3, ExtJs 4

Домашняя бухгалтерия на ExtJS 4, PHP 5, SQLite 3.
Демо - http://demobuh.skahin.ru
Пароль и логин: admin

Описание возможностей и онлайн использование: http://buh.skahin.ru

Разработка и исходный код: https://github.com/pihel/cash

ORACLE: Ускорение pl/sql циклов и пользовательских функций

Продолжаю тему оптимизации запросов ORACLE, хотелось бы коснуться циклов по SQL запросам в PLSQL.
Возможно, многим это уже известно, но все же.
Чаще всего запросы в plsql пишутся следующим образом:

PROCEDURE increase_salary (
   department_id_in   IN employees.department_id%TYPE,
   increase_pct_in    IN NUMBER)
IS
BEGIN
   FOR employee_rec
      IN (SELECT employee_id
            FROM employees
           WHERE department_id =
                    increase_salary.department_id_in)
   LOOP
      UPDATE employees emp
         SET emp.salary = emp.salary + 
             emp.salary * increase_salary.increase_pct_in
       WHERE emp.employee_id = employee_rec.employee_id;
   END LOOP;
END increase_salary;

У такого подхода есть существенный минус - для каждой записи из SELECT, ORACLE приходится менять контекст выполнения с SQL на PLSQL.
Такого рода операцию можно выполнить без смены контекста 2 способами:
* UPDATE без цикла - в простом варианте ДА, но не все циклы так просты.
* Использовать BULK COLLECT и FORALL
С BULK COLLECT и FORALL запрос получится следующий:

CREATE OR REPLACE PROCEDURE increase_salary (
  department_id_in   IN employees.department_id%TYPE,
  increase_pct_in    IN NUMBER)
IS
  TYPE employee_ids_t IS TABLE OF employees.employee_id%TYPE
          INDEX BY PLS_INTEGER; 
  l_employee_ids   employee_ids_t;
BEGIN
  SELECT employee_id
     BULK COLLECT INTO l_employee_ids
    FROM employees
   WHERE department_id = increase_salary.department_id_in;

  FORALL indx IN 1 .. l_employee_ids.COUNT
     UPDATE employees emp
        SET emp.salary =
                 emp.salary
               + emp.salary * increase_salary.increase_pct_in
      WHERE emp.employee_id = l_employee_ids (indx);
END increase_salary;

Разберем код:
* Создаем Тип "employee_ids_t" - это ассоциативный массив, где ключ PLS_INTEGER, а значение = employees.employee_id%TYPE
* l_employee_ids - это переменная типа employee_ids_t
* BULK COLLECT INTO - поместить отобранные select идентификаторы в коллекцию l_employee_ids. Помещаются все записи разом, в отличии от обычного INTO (одна запись)
* Конструкция FORALL - выполняет UPDATE столько раз, сколько записей в коллекции l_employee_ids и используя данные из нее.

Стоит заметить, что FORALL это не цикл, а конструкция языка, так что он имеет ряд особенностей:
* Внутри FORALL может быть только 1 DML запрос. Если нужно несколько запросов, то нужно использовать несколько FORALL
* При выполнении FORALL не происходит переключения контекста. Весь UPDATE выполняется за 1 раз, что дает значительное преимущество в скорости.

Стоит заметить, что время на смену контекста также расходуется при вызове пользовательских функций в DML запросах.
К примеру:

FUNCTION betwnstr (
   string_in      IN   VARCHAR2
 , start_in       IN   INTEGER
 , end_in         IN   INTEGER
)
   RETURN VARCHAR2 
 
....

SELECT betwnstr (last_name, 2, 6) 
  FROM employees
 WHERE department_id = 10

* Контекст будет сменен столько раз, сколько записей отберется в SELECT
Чтобы избежать смены контекста, можно :
* Попробовать объявить функцию как INLINE "PRAGMA INLINE "
* Или как используемую только в DML "PRAGMA UDF"
* Но лучший вариант в данном случае - отказаться от функции и сознательно денормализировать запрос до чистого SQL.

На основе http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52plsql-1709862.html

ORACLE 11-12: 1Z0-047 Oracle Database SQL Expert. Новые вопросы 15 сентября 2014.

С 15 сентября 2014 в экзамен 1Z0-047 включены новые темы вопросов по Oracle 11-12

• Limit the rows that are retrieved by a query
• Create queries using the PIVOT and UNPIVOT clause
• Use pattern matching to recognize patterns across multiple rows in a table
• Use character, number, and date and analytical (PERCENTILE_CONT, STDDEV, LAG, LEAD)
• Use the cross_outer_apply_clause
• Create a lateral inline view in a query
• Use subqueries
• Truncate tables, and recursively truncate child tables
• Use 12c enhancements to the DEFAULT clause, invisible columns, virtual columns and identity columns in table creation/alteration
• Identity Columns
• Invisible columns
• Virtual columns
• Create simple and complex views with visible/invisible columns
• Grant privileges on tables and on a user
• Create and maintain indexes including invisible indexes and multiple indexes on the same columns
• WITH Clause Enhancements in Oracle Database 12c

Новое расположение

Неочевидные вопросы сертификации 1Z0-047 Oracle Database SQL Expert

Продолжение. Новые вопросы с сентября 2014

1. Нельзя писать алиас у колонки в select по которой происходит join в using (или * с алиасом таблицы). Это же относится к natural соединениям.

   SELECT key
   FROM A FULL JOIN B
   USING (key)
   

2. Вставка в несколько таблиц
   * INSERT ALL - может быть без WHEN
   * INSERT ALL - поумолчанию
   * INSERT FIRST - при совпадении WHEN, остальные не просматриваются
   * INSERT ALL - просматриваются все ветвления WHEN
   * Может быть несколько INTO в одном WHEN
   * Нельзя использовать sequence в SELECT запросе множественном INSERT (но можно в VALUES)
   

   INSERT [ALL|FIRST]
    WHEN <..> THEN
     INTO A (a1,a2) VALUES(b1,b2)
    WHEN <..> THEN
     INTO C (c1,c2) VALUES(b2,b1)
    ELSE
     INTO D (d1,d2) VALUES(b1,b2)
   SELECT
    b1, b2, b3 FROM B
   

3. MERGE dml операция:
   * Преимущество - один проход
   * D/I/U - возможные операции
   * WHERE можно писать только по таблице из USING
   

   MERGE INTO <..>
   USING A
     <..>
   WHEN MATHED THEN UPDATE SET <..>
   [WHERE <..>]
   

4. WHERE выполняется до SET.
   Это значит, что в SET можно писать невалидные значения, если WHERE их отсечет.
5. AND имеет больший приоритет, чем OR
6. Арифметические операции имеют больший приоритет, чем SQL операторы сравнения
7. FROM необязателен в DELETE
8. CLOB, BLOB, TIMESTAMP WITH TIMEZONE (далее TS WITH TZ) - нельзя использовать в PK
   , а TS WITH LOCAL TZ - можно
9. Размеры полей:
   * CHAR -> CHAR(1)
   * INTERVAL DAY -> INTERVAL DAY(2)
   * NUMBER(2,-3) - округлит! (не обрежет) до 3 знака перед запятой
   * VARCHAR - обязательно указывать размерность
   * VARCHAR(1 CHAR), если база в cp1251 - это 1 байт, если UTF8 , то от 2 байт
   
10. UNION, MINUS, INTERSECT - set операторы
    Сортировка в них возможна только по алиасу или позиции в самом конце.
11. Допускается только двойное вложение агрегирующих функций (3ий нельзя)
    MAX(SUM(<..>))
12. Типы соединений:
    * cartesian -> cross
    * nonequijoin -> < , >
    * full -> outer
    * inner -> natural
    
13. FK можно создавать по полям:
    * Одного типа, но разных разных размерностей
    * FK может указывать на PK и на UNIQUE index поля
14. UNUSED поле - поле аналогично дропнутому, но физически из таблицы не вычищено:
    

    SET UNUSED COUMN <..>; --помечаем неиспользуемым
    ALTER TABLE T DROP UNUSED COUMNS; --физическое удаление неиспользуемых колонок
    

15. По V$ представлениям нельзя делать запросы, т.к. их структура может меняться со временем.
    Если же в этом есть необходимость, то нужно сперва создать копию.
16. Нет отдельных прав на создание FK, constraints, index
    Такие права даются вместа с правами на таблицу.
17. Non-schema objects - это users, roles, public synonims
18. Допускается сравнение systimestamp и date типов
19. Если в CREATE SEQUENCE задана опция CYCLE, то MAXVALUE может быть отрицательным (т.к. START тоже может быть отрицательным)
20. Нельзя создавать NOT NULL constraint отдельно от создания таблицы (alter)
21. UNIQUE в SELECT аналогичен DISTINCT
22. ROWNUM нельзя использовать с * без алиса
    

    SELECT ROWNUM, * --нельзя
    

23. * NULL поля при ASC сортировке идут последними
    * при DESC первыми
    Можно указать принудительно:

    ORDER BY <..> nulls last[first]

24. При выборке из TS WITH LOCAL TZ к дате вставки добавляется разность между зоной вставки и зоной выборки.
25. MONTHS_BETWEEN(большая дата, меньшая дата) = Дробное чило
    Если наоборот, то отрицательное число.
26. MEDIAN (среднее по порядку) и AVG не приминают в параметре строку
27. Все групповые (агрегирующие) функции принимают только NOT NULL значения.

28. RANK( expression1, ... expression_n ) WITHIN GROUP ( ORDER BY expression1, ... expression_n )
    

    Ранк группы, если записей с одним ранком несколько, то им проставляется один номер.
29. HAVING может использоваться только в SELECT после WHERE (можно даже без GROUP BY)
30. * > ALL (<..>) - TRUE, если все строки подзапроса больше, или подзапрос ничего не! вернул
    * > SOME (<..>) - TRUE, если хотябы одна строка подзапроса больше, FALSE - подрапрос ничего не вернул
    
31. Нельзя делать сложные (составные) столбцы в VIEW и Create table as select без алиасов (общие правила именования столбцов таблиц)
32. Обычные synonim (не public) также распространяется на всю бд, но права даны только текущему пользователю.
    В случае public synonim права автоматически даны всем пользовалям.
33. Перекомпиляция VIEW:
    

    ALTER <..> compile;

34. ALTER TABLE T ENABLE NOVALIDATE CONSTRAINT <..>;

    Не проверять constraint у уже сущесвующих записей (будет проверяться только у новых)

35. SET CONSTRAINT <..> DEFERRED;

    * Не проверяет constraint до первого commit;
    * После прохождения commit, constraint автоматом сменяется на immdiate - проверять все данные сразу.
36. Создание constraint с INDEX
    

    CREATE TABLE T (
     <..>,
     constraint <..> UNIQUE (<...>)
      USING INDEX (CREATE INDEX <..> ON <..> (<..>) )
    )
    

    * Такой индекс можно создать только по UNIQUE и PK constraint
    * NOT NULL ограничение нельзя использовать в USING INDEX и CHECK (только в inline)
37. Перманентно дропнуть таблицу без возможности восстановить из корзины.

    PURGE TABLE <..>;
    

38. Восстановление таблицы на состояние в прошлом (из корзины).
    * SCN - Номер коммита. Можно получить используя пакет FLASHBACK.get_system_change_number

    FLASHBACK TABLE <..> TO SCN <..>
    

    * TIMESTAMP - на определенное время

    FLASHBACK TABLE <..> TO TIMESTAMP <..>
    

    * RESTORE POINT - точка восстановления ( CREATE RESTORE POINT <..> )

    FLASHBACK TABLE <..> TO RESTORE POINT <..>
    

    * BEFORE DROPS - восстановит таблицу, индексы, гранты, constraints ( все, кроме FK )

    FLASHBACK TABLE <..> TO BEFORE DROPS;
    

39. * Получение данных на определенное время (нельзя использовать подзапросы)

    SELECT * FROM <..> 
      * as of timestamp('дата','формат'); --на точное время
      * as of timestamp systimestamp - intreval '0 00:01:30' DAY TO SEC; --полторы минуты назад
      * VERSION BETWEEN [TIMESTAMP 'TS1' AND 'TS2'] --между датами
                        [SCN '1' AND '2'] --между коммитами
    

    * Показ версии ( в историю попадают записи перед commit)

    SELECT t.*, version_operation, RAWTOHEX(version_xid)
    FROM T
    VERSIONS BETWEEN TIMESTAMP minvalue AND maxvalue;
    

40. Включение FlashBack
    * DDL (может указана в настройках бд)

    ALTER SESSION SET RECYCLEBIN=ON;
    

    * DML (может быть настроено при создании таблицы)

    ALTER TABLE <..> ENABLE ROW MOVEMENT;
    

41. EXTERNAL - Внешние метаданные
    * Можно делать select
    * Нельзя: index, constraint, update, delete
    * Хранится в папке на сервере
    * Создание (синтаксис схож с синтаксисом SQL Loader )

    CREATE TABLE <..> (<cols>)
    ORGANIZATION EXTERNAL
    ( ... LOCATION ('file.csv') );
    

42. Советы по оптимизации составных индексов:
    * Самые часто используемые столбцы должны идти первыми
    ** Поиск по первым столбцам - range scan
    ** Поиск по другим - scip scan
    Поиск по части индекса, для каждого уникального значения префикса индекса.
43. INTERSECT - убираем дубликаты строк.
    Пересечение 2ух одинаковых строк с 2умя другими даст одну.
44. CUBE/ROLLUP по одному столбцу добавляет лишь 1 строку - общий итог.
45. * Системные представления хранятся в схеме SYS
    * DICTIONARY - описание всех таблиц
    * USER_<..> таблицы не имеют OWNER столбца
46. Иерархические запросы:
    * Сортировка по полю внутри уровня.

    ORDER BY sibling by <..>;
    

    * Полный путь до уровня

    SYS_COMMENT_BY_PATH(col, '/')
    

    * Вывод данных в зависимости от уровня

    CONNECT_BY_ROOT COL -- данные из верха иерархии
    CONNECT_BY_LEAF COL -- данные из самого низа (листка)
    CONNECT_BY_CYCLE COL -- данные, где начался цикл
    

    * WHERE идет и выполняется перед START WITH
47. * Выдача прав с возможностью дальнейшей передачи (ADMIN OPTION)

    GRANT CREATE ANY TABLE TO <..> WITH ADMIN OPTION;
    

    * REVOKE права - отбирает не только сами права, но и возможность передачи (если были даны через ADMIN OPTION )
48. UNION с любым другими SET операторами аналогичен DISTINCT

    SELECT 1 FROM DUAL
    UNION ALL
    SELECT 1 FROM DUAL
    UNION
    SELECT 2 FROM DUAL
    

    Запрос даст 2 строки, не смотря что первый оператор UNION ALL.

Bitrix: применяем стили визуального редактора правильно

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

Для решения этой проблемы я решил обернуть весь текст из визуального редактора в div с определенным классом и все стили наследовать от него.
Т.е. в файле стилей нам можно будет прописать оформление для всех элементов наследников этого класса. Тогда оформление будет выглядеть одинаково как в самом визуальном редакторе, так и в любой части страницы, т.к. весь код будет обернут в этот div.
Остается только вставлять этот див автоматически в визуальный редактор.
Для этого в init.php добавляем обработчик события подключения визуального редактора и при его инициализации создаем div в который поместим весь контент.

<?php
//событие подключения визуального редактора
AddEventHandler("fileman", "OnIncludeHTMLEditorScript", array("MyHandlers", "OnIncludeHTMLEditorScript"));


class MyHandlers {

   public static function OnIncludeHTMLEditorScript()
   {
      ?>
      <script type="text/javascript">
      //получаем указатель на полотно визуального редактора
      BXHTMLEditor.prototype.SetView_ = BXHTMLEditor.prototype.SetView;
      BXHTMLEditor.prototype.SetView = function (sType) {
         var result = this.SetView_(sType);
         var html = undefined;
         if(BX.browser.IsIE()) {
            try {
               html = this.pEditorDocument.body;
            } catch (e) {
               //
            }         
         } else {
            try {
               html = this.pEditorWindow.document.body;
            } catch (e) {
              //
            }
         }
         if( sType == "html" && html ) {
            //обертываем в DIV, если его еще нет
            if( html.innerHTML.indexOf('<div class="content">') == -1 ) {
              html.innerHTML = '<div class="content">'+html.innerHTML+'</div>';
            }
         }
         return result;
      };
      </script>
      <?
   }
}
?>

Две заметки о Bitrix

1. Для создания сложных форм на bitrix я использую кастомный компонент в котором вытаскиваю все данные о инфоблоке с помощью такого запроса:

function getIblockProps($id) {
 global $DB;
 $sql = "SELECT b.id, b.code, b.name,
   -----
   p.id as pid, p.code as pcode, p.name as pname, p.SORT psort, 
    UPPER(CASE 
     WHEN p.USER_TYPE IS NOT NULL THEN p.USER_TYPE
     WHEN p.PROPERTY_TYPE = 'S' AND p.ROW_COUNT > 1 THEN 'T'
     ELSE p.PROPERTY_TYPE
    END) as ptype, 
    CASE WHEN p.MULTIPLE = 'Y' THEN 1 END as pmultiple, CASE WHEN p.MULTIPLE = 'Y' THEN p.MULTIPLE_CNT END pmultiple_cnt, 
   CASE WHEN p.LINK_IBLOCK_ID > 0 THEN p.LINK_IBLOCK_ID END as piblock, 
   CASE WHEN p.IS_REQUIRED = 'Y' THEN p.IS_REQUIRED END preq, p.HINT as phint,
   -----
   e.ID as eid, e.VALUE as evalue, e.DEF as edef, e.SORT as esort
  FROM b_iblock b , b_iblock_property p
  LEFT JOIN b_iblock_property_enum e
  ON(e.property_id = p.id)
  WHERE b.id = %u
  AND b.id = p.IBLOCK_ID AND p.ACTIVE = 'Y'
  ORDER BY p.sort,e.SORT";
 $rs = $DB->Query( sprintf( $sql, intval($id) ) );

 $rows = array();
 while ($row = $rs->Fetch()) {
  $rows[] = $row;
 }

 return $rows;
}

Запрос берет информацию о полях и свойствах инфоблока. Если поле ссылочное, то функцию можно вызывать рекурсивно.

2. Особенность создания товарных предложений через код.
Создали мы товарное предложение через код:

$el = new CIBlockElement;  
$id = $el->Add($arFields);

Задали цену:

CPrice::SetBasePrice( $id,  $arFields['PRICE'],  "RUB", 1);

Заполнили остатки:

$arFields = Array(
  "PRODUCT_ID" => $id,
  "STORE_ID" => 1,
  "AMOUNT" => $v['Количество'],
 );
 
CCatalogStoreProduct::Add($arFields);

Вроде все. Но товар не покупается, хотя все атрибуты присутствуют.

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

$arFields = array('ID'=>$id, 'PRICE_TYPE' => 'S', 'QUANTITY' => floatval($f['Количество']));
CCatalogProduct::Add($arFields);

SQLite: две особенности этой базы

Хочу рассказать про 2 отличия SQLite от других БД, решение которых придется долго искать, если вы только начинаете работать с ней.

1. Приведение к верхнему регистру UPPER юникод строк UTF8.
Необходимость в этом у меня появилась при регистронезависимом поиске по подстроке LIKE и как оказалось поумолчанию эта функций в SQLite нет и предлагается самим ее реализовать.
В интернете множество вариантов решения через настройку SQLite и доустановку дополнительных пакетов. Но мне такой способ не подходил, т.к. на шаред хостинге это не сделать.

К счастью SQLite позволяет делать собственные пользовательские функции, чем мы и воспользуемся.
Далее код на php для создания собственной функции UPPER - UPPER_UTF8.

createFunction('UPPER_UTF8', 'upper_ru', 1);
//array('SQLITE_DB', 'upper_ru') - если используете в составе класса 'SQLITE_DB'

?>

После этих нехитрых манупуляций в составе SQLite появится новая функция UPPER_UTF8 и с помощью ее можно будет делать регистронезависимые запросы:

SELECT 
  t.* 
FROM
  table t
WHERE
  UPPER_UTF8(t.col) LIKE UPPER_UTF8('%кАкойТо Запрос%')

2. Правильное создание индексов для поиска по диапазону в БД SQLite.
Если вы хотите осуществить быстрый поиск по таблице, то во всех субд для этого необходимо создать индекс. Но в SQLite еще нужно соблюсти правильную последовательность.
Так если вы создаете индекс по нескольким полям, то столбец диапозона должен обязательно идти последним.

Далее 2 примера, сначала неправильный:

--таблица
CREATE TABLE "cashes" (
    "id" INTEGER PRIMARY KEY  NOT NULL,
    "date" DATE NOT NULL,
    "uid" INT(10) NOT NULL DEFAULT ('1'),
    "visible" TINYINT(4) NOT NULL DEFAULT ('1')
);

--индекс
CREATE INDEX "XIF_CASHES_DUV" on cashes (date DESC, uid ASC, visible ASC);

--вызов плана
EXPLAIN QUERY PLAN SELECT
      c.id, c.uid, c.date
     FROM cashes c
     WHERE
      c.date BETWEEN '2013-04-01' AND '2013-06-01'
      AND c.uid = 1 AND c.visible = 1
     ORDER BY
      c.date;

--план
SEARCH TABLE cashes AS c USING INDEX XIF_CASHES_USR (uid=?) (~2 rows)

А дальше, как нужно было правильно создавать индекс. Поле даты - поле диапозона, должно было идти последним:

--индекс
CREATE INDEX "XIF_CASHES_DUV" on cashes (uid ASC, visible ASC, date DESC);

--план запроса
SEARCH TABLE cashes AS c USING INDEX XIF_CASHES_DUV (uid=? AND visible=? AND date>? AND date<?) (~42 rows)

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

Firefox: пишем расширение для браузера

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

Внедерение своего скрипта в тело страницы

//защищенная часть скрипта - оборачиваем в анонимную функцию, чтобы не было повторов имен переменных
(function() {
    var YT_global_doc = undefined;

    var YT_loader = new function(){};
    YT_loader.run = function(e) {
        //проверяем адрес сайта
	var unsafeWin = e.target.defaultView;
	var unsafeLoc = new XPCNativeWrapper(unsafeWin, 'location').location;
	var href = new XPCNativeWrapper(unsafeLoc, 'href').href;
	if (!href.match(/^http:\/\/www\.xxxx\.com\/watch(.*)?$/i) )
	      return;

        //обращаемся к dom сайта
	YT_global_doc = e.target.defaultView.document;

	if(YT_global_doc) {

          //подгружаем наш скрипт
	  var script = YT_global_doc.createElement( 'script' );
	  script.type = 'text/javascript';
	  script.src = 'chrome://xxx/content/yyy.js';
	  YT_global_doc.body.appendChild(script);

	}
    };

    var yt_load = function() {
        //событие загрузки вкладки с сайтом, выполняется каждый раз при загрузке страницы
        window.document.addEventListener("DOMContentLoaded", YT_loader.run, true);
    };
    //событие загрузки приватной части - выполняется один раз
    window.addEventListener("load", yt_load, false);

})();

Правильное создание новых узлов в DOM
Внутренними правилами firefox запрещено обращаться и работать с dom через innerHtml. Взамен этого необходимо пользоваться шаблонами. В своих аддонах я пользуюсь JQuery Templating.
Вот пример:

$("#pv_actions").prepend (
      $("<a>", {
	href: 	"#",
	target: "_blank",
	text:	"Загрузить оригинал",
	id:	"pv_down_link"
      })
      .css("font-weight", "bold")
      .bind("click", function (o) {
	//
      })
);

Взаимодействие с защищенной частью скрипта
Аддон состоит из 2 частей: защищенной и внедренной (небезопасной части). В безопасной части можно работать с апи браузера: сохранять файл, делать ajax запросы и т.д.
Взаимодействовать эти две изолированные части могут только по средствам событий.
Рассмотрим пример: узнать размер файла.
В незащищённой части создаем событие с нашими параметрами.

  //yt_getFileSizeEvent - название события
  var element = document.createElement("yt_getFileSizeEvent");
  element.setAttribute("attribute_url", "адрес до файла" );
  element.setAttribute("attribute_id",  "индекс файла" );
  document.documentElement.appendChild(element);
  var evt = document.createEvent("Events");
  evt.initEvent("yt_getFileSizeEvent", true, false);
  element.dispatchEvent(evt);

В защищенной части ловим событие:

document.addEventListener("yt_getFileSizeEvent", function(e) { YT_secureEvent.getFileSize(e); }, false, true);

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