Платформа WT
Практика
Синтаксис
База знаний

Шпаргалки и дорожные карты

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

RegExp шпаргалки

1. Генерация UPDATE\Field(форма) из SELECT

Подобный подход сохраняет много времени при работе с большими таблицами .

Допустим у нас есть запрос такого вида

        SELECT
          bla_bla_id as "BlaBlaId",
          enabled as "Enabled",
          priority as "Priority",
          value as "Value",
          title as "Title"
        FROM
          public.bla_bla;

Выделим блок между SELECT и FROM и вставим в любой редактор с поддержкой регулярных выражений или на сайт: https://regex101.com/

Регулярное выражение, которое мы будем использовать для поиска:

\s*(\S*)[^"]*"([^"]+)",*

Заменяем всё на:

$1 = "$2",\n

И получаем запрос вида:

UPDATE
    public.bla_bla
SET
    /*вставляем то, что заменилось по регулярке сюда, кроме первой строки, 
     *убираем последнюю запятую */
WHERE
    /*сюда переносим первую строку без запятой*/
;
/*Получаем*/
UPDATE
 public.bla_bla
SET
    enabled = "Enabled",
    priority = "Priority",
    value = "Value",
    title = "Title"
WHERE
    bla_bla_id = "BlaBlaId"

Генерация <Field Name="BlaBlaId"/> выглядит так же. Вот выражение для замены:

<Field Name="$2"/>\n

2. Генерация переменных функции из DDL таблицы.

Допустим у нас есть таблица вида

CREATE TABLE public.bonus (
	bonus_id int DEFAULT nextval('public.bonus_id_seq'::regclass) NOT NULL,
	orders int NOT NULL,
	summ numeric NOT NULL,
	percents numeric NOT NULL,
	CONSTRAINT pk_bonus_id PRIMARY KEY (bonus_id)
);

и нам нужно написать для неё функции на update и insert. И если для 5 переменных всё просто, то уже при 20-30 переменных руки сами тянутся к простому способу.

Вставляем все столбцы, которые нас интересуют в регулярное выражение для получения имён переменных:

(\S+)([( int)( numeric)( bigint)( boolean)( character)( character varying)( date) (double precision)( integer)( interval)( smallint)( text)( time)(timestamp without time zone)(timestamp with time zone)]*)( DEFAULT| nextval\([^\)]*\)| NOT| NULL)*,

А в замене указываем 

v_$1$2;

Получаем набор переменных вида

v_bonus_id int;
v_orders int;
v_summ numeric;
v_percents numeric;
v_words character varying;

Если нужно написать update, то таким же способом пишем

$1 = v_$1,

Получаем поля для update

bonus_id = v_bonus_id,
orders = v_orders,
summ = v_summ,
percents = v_percents,
words = v_words,

Регулярное выражение для выделения всего, включая тип переменной:

(\S+)( int| DEFAULT| nextval\([^\)]*\)| NOT| NULL| numeric| bigint| boolean| character| varying| varchar| date| double| precision| interval| smallint| text| time| timestamp| without| time| zone| with)*,

3. Чистка форм v2.

Раньше использовался аттрибут ObjectType="typeObject", который современный редактор воспринимает как ошибку. Используя регулярное выражение

ObjectType="[^"]*"

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

4. Полезные регулярные выражения, которые экономят время

В разных ситуациях нужны разные регулярные выражения, список, что написался\собрался за годы:

Формат.
Что делает
Регулярное выражение
На что заменять( или пометка, что для поиска)

Удаление всех комментариев из файла:
<!--(.*?)--> 

Проверка\поиск почты
^[A-Z0-9._%+-]+@[A-Z0-9-]+.+.[A-Z]{2,4}$
Поиск

Регулярка на проверку даты, с учётом високосных годов. Разделители . - или /
^(?:(?:31(\/|-|\.)(?:0?[13578]|1[02]))\1|(?:(?:29|30)(\/|-|\.)(?:0?[1,3-9]|1[0-2])\2))(?:(?:1[6-9]|[2-9]\d)?\d{2})$|^(?:29(\/|-|\.)0?2\3(?:(?:(?:1[6-9]|[2-9]\d)?(?:0[48]|[2468][048]|[13579][26])|(?:(?:16|[2468][048]|[3579][26])00))))$|^(?:0?[1-9]|1\d|2[0-8])(\/|-|\.)(?:(?:0?[1-9])|(?:1[0-2]))\4(?:(?:1[6-9]|[2-9]\d)?\d{2})$

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

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

Дорожные карты работы с v3

Добавление печатной переменной

  1. Добавить переменную в запрос SQL

  2. Добавить переменную на форму

  3. Проверить, что новая переменная работает при печати

  4. Определить категорию переменной, после чего создать саму переменную в schema.document_template_variable, где в title указываем имя переменной, которое указывается в word(<#Variablename#>, например). name выбираем произвольно.

  5. По name переменной создать перевод описания в public.strings дял всех языков, что используются в проекте

  6. Если в проекте используются фильтры, то добавить эту переменную в таблицу schema.document_template_use_variable

Добавление отчета

  1. INSERT INTO schema.report(title,name) values ('Отчет по бабкам', 'report_grand_mother');

  2. Поправить collect.user_info

  3. Добавить отчет в запросы для страницы UserEdit.xml и добавить её в запросы. Добавить переменную в массив для сохранения и в массив сверки.

  4. Сделать ссылку на отчет в StartForm

  5. Создать отчет

Добавление уведомлений в Carrent

  1. Создать 2 поля в настройках: уведомлять о и значение через сколько уведомлять. Добавить изменения на форму CarrentSettings.

  2. Сделать необходимые правки в основной базе(rent_save\смена логики\ исправление работы залогов\ добавление новых полей в аренду ит.п.)

  3. Добавить новый тип уведомления INSERT INTO carrent.notification_type(title, name, template) VALUES ('Имя уведомления', 'NOTIFICATION_NAME', 'TEMPLATE_NAME');

  4. Добавить в public.strings для каждого из языков шаблон для NOTIFICATION_NAME и TEMPLATE_NAME. Для NOTIFICATION_NAME короткое имя уведомления: возврат залога. Для TEMPLATE_NAME расширеная версия для частного случая: Пора вернуть залог в аренде {car_title} от {rent_date_start}.

  5. Внести правки в функцию rent_add_notifications.

  6. Внести правки во view carrent.active_notifications

  7. В файле CarrentNotificationList.xml сделать условие на проверку по имени уведомления, в команду RentEditFormShowCommand при необходимости внести изменения на открытие нужной вкладки.

  8. CarrentStart - проверить в настройках галочку об уведомлении. Сделать SecondaryConnection по имени уведомления, кондишн, что уведомлений больше 0, кондишн проверки галки, сделать элемент меню в меню уведомлений

  9. В NotificationListCountVariable добавить счетчик с новым уведомлением

SQL шпаргалки

0. Мы все в большинстве своём не DBA, но это не отменяет того, что хорошим тоном будет изучение своих баз данных, чтобы привести их к человеческому виду.

  1. На версиях до PostgreSQL 12 CTE очень плохо работают с оптимизатором. Из-за этого нужно обязательно проверять через EXPLAIN ANALYZE работу вашего запроса. Тем не менее не стоит создавать десять вложенных друг в друга подзапросов с именем T\M\PPC. Давайте им хотя бы какое-то время и если EXPLAIN analyze не уничтожает ваш запрос с CTE, то используйте CTE.

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

  3. Сложные функции на plpgsql очень плохо работают на дистанции, если есть возможность, то постарайтесь делать это либо чистым запросом, или на уровне языка. Как в очередной раз показывает практика. Если нужно сделать что-то сложнее 5-10 операций вычислительных, кроме CRUD, то лучше сделать это на языке.

  4. Введите правило, добавляя Foreign key к чему - то большому, создавайте индекс по этому полю, если у вас есть частные запросы. Например, если мы делаем foreign_key на client_id в таблице phone, то лучше сразу создать на поле client_id в таблице phone индекс, если существует запрос вида

SELECT
    phone_id,
    phone_number
FROM
    public.phone
WHERE
    client_id = {ClientId};

  1. Получение кол-ва дней между двумя датами с округлением вверх

ceil(EXTRACT(epoch from date_finish::timestamp - date_start::timestamp)/(3600*24)

  1. Чтобы не уродовать LATER JOIN нижнюю часть запроса и не усложнять работу оптимизатору, если фильтрация есть в верхней части, можно сделать запрос вида(отсылка к номеру 1, но тем не менее, LATERAL зачастую будет ещё хуже).

, last_comment as (
     SELECT DISTINCT ON (rent_id)
        final.rent_id,
        text,
        date_created
      FROM
        final
        LEFT JOIN carrent.rent_comment USING(rent_id)
      ORDER BY rent_id, date_created DESC
)

Чтобы получить то, что вы получали LATER JOIN.

  1. Сбор дубликатов

SELECT
    number,
    COUNT(*)
FROM
    carrent.car
GROUP BY 
    number
HAVING
    COUNT(*) > 1

  1. Проверка нормально написанных настроек и запросов для базы:

На правильно настроенной и грамотно собранной БД ratio будет выше 0.9. Если вы читали статью про индексы, то знаете, что оптимизатор не всегда выбирает способ сбора запроса в оперативной памяти, эти значения как раз относятся к этой истории.

SELECT sum(heap_blks_read) as heap_read,
       sum(heap_blks_hit)  as heap_hit,
       sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM 
  pg_statio_user_tables;

  1. Запрос на использование индексов. Если у таблицы более 40-50 тысяч записей и низкий процент использования индекса, то это повод задуматься над тем, чтобы проанализировать запросы\базу и добавить нужные индексы

SELECT relname,   
       100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used,   
       n_live_tup rows_in_table 
FROM pg_stat_user_tables 
WHERE seq_scan + idx_scan > 0 
ORDER BY n_live_tup DESC;

  1. Поиск индексов, которые не использовались. Необязательно их удалять, но если создан большой индекс, который никак не используется уже несколько месяцев, то стоит задуматься об его пользе. Если же это редко используемый индекс для огромной таблицы, то может быть стоит подумать о сводных полях за какое-то время, чтобы не лезть каждый раз в таблицу, не сканировать её по 30 секунд, а просто получать все данные, до 1 января прошлого года? В частности это относится к таблице в carrent'ах снизу справа с прибылью


SELECT schemaname, relname, indexrelname
FROM pg_stat_all_indexes
WHERE idx_scan = 0 and schemaname <> 'pg_toast' and  schemaname <> 'pg_catalog'

  1. Список активных запросов

SELECT pid, age(clock_timestamp(), query_start), usename, query, state
FROM pg_stat_activity
WHERE state != 'idle' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

  1. PgAdmin3 не поддерживает версии постгреса старше 13, а переход на PostgreSQL 15 кажется с каждым годом всё более важным и нужным. Чтобы не страдать потом муками отвыкания от устаревшего морально клиента для работы с базами данных, попробуйте найти для себя что-то более удобное:

  • psql - представлен больше как шутка, мало любителей работать в cmd

  • PgAdmin4 - просто привычный клиент, криво написанный, зато бесплатный. Как его использовать не очень ясно.

  • DataGrip - хороший клиент. Shareware

  • NaviCat - шикарный клиент, Shareware, причем дорогой

  • Dbeaver - FreeWare клиент, написанный на java. Редактор писался теми, кто очень любит Eclipse, поэтому и настройки, и работа с ним очень похожа. В целом один из лучших представителей бесплатных клиентов, при этом быстро развивающийся.

Если вы считаете, что есть что-то ещё, что нужно включить в этот список на сегодняшний день, пишите на почту crabiki4@gmail.com

Last updated