Прекрасного всем завтрака

Макросы в pgAdmin. Генерация шаблонов select,insert,update,delete,truncate для конкретной таблицы

Во время написания кода sql- или plpgsql-функций  хочется уменьшить время, которое затрачивается на реализацию типовых фрагментов. К таким фрагментам относятся операторы select, insert,update,delete,truncate  при работе с  таблицами. К сожалению в pgadmin отсутствует функция автозавершения набираемого текста и поэтому нет возможности быстро генерировать команды манипуляции с таблицами с выбором шаблонов этих  команд из контекстного меню.

Чтобы восполнить  этот пробел я написал pgadmin-макрос, генерирующий шаблоны команд select, insert,update,delete,truncate для таблицы с подстановкой соответствующих полей этой таблицы.

Текст макроса:

SELECT table_schema,
       TABLE_NAME,
       'select ' || string_agg(COLUMN_NAME, ',') || ' from ' || table_schema || '.' || TABLE_NAME AS query_select,
                                                                                                     'insert into ' || table_schema || '.' || TABLE_NAME || '(' || string_agg(COLUMN_NAME, ',') || ') values()' AS query_insert,
                                                                                                                                                            'update ' || table_schema || '.' || TABLE_NAME || ' set ' || string_agg(COLUMN_NAME, '= ,') || ' where' AS query_update,
'delete from '|| table_schema || '.' || TABLE_NAME || ' where ' AS query_delete,
                                                      'truncate table ' || table_schema || '.' || TABLE_NAME || ' restart identity cascade' AS query_truncate
FROM information_schema.columns
WHERE NOT (table_schema ILIKE 'pg_%'
           OR table_schema ILIKE 'information_%')
AND TABLE_NAME ILIKE ('%' || trim('$SELECTION$') || '%')
GROUP BY TABLE_NAME,
         table_schema;

Пример:

1. Создадим таблицу table1

CREATE TABLE table1( id serial PRIMARY KEY, name varchar(255), code varchar(20), datetime TIMESTAMP DEFAULT now());

2. Выделим  название "table1" и выполним наш макрос

3. Результатом выполнения макроса в pgadmin будет следующая таблица

Тексты полученных шаблонов команд для таблицы будут следующие:

select id,name,code,datetime from public.table1

insert into public.table1(id,name,code,datetime) values()

update public.table1 set id= ,name= ,code= ,datetime where

delete from public.table1 where

truncate table public.table1 restart identity cascade

4. Далее можно скопировать текст нужного шаблона и использовать в написании sql-кода. 

Благодаря макросу мы фактически  получили тексты 5 основных шаблонных команд для работы с таблицей - select,insert,update,delete,truncate.  Если таблица большая и содержит  множество полей - это тем более полезно, не нужно выполнять дополнительные действия по уточнению наименований полей. Таким образом, этот макрос позволяет сократить время работы и увеличить производительность программиста. 

Посмотреть работу макроса можно в следующем на примере по ссылке 

Макросы в pgAdmin. Количество записей в таблице и размер в мегабайтах.

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

1. Макрос для получения количества строк в таблице, чье название выделено в окне Query Tool.
   
   

   SELECT COUNT(*) FROM $SELECTION$

   
   
2. Макрос, позволяющий определить размер объекта, в название которого входит выделенный в Query Tool текст. Среди таких объектов могут быть таблицы, индексы, последовательности (sequence).
   
   

   SELECT pg_class.relkind AS "Тип", pg_namespace.nspname AS "Схема", pg_class.relname AS "Имя", 
   pg_class.relpages::FLOAT*8192/1024/1024 AS "Размер (Мб)", 
   pg_class.reltuples AS "Записей" FROM pg_class
   LEFT JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid 
   WHERE 
   NOT pg_class.relname LIKE 'pg_%' 
   AND NOT (pg_namespace.nspname ILIKE 'pg_%' OR pg_namespace.nspname ILIKE 'information_%') 
   AND pg_class.relname ILIKE ('%' || TRIM('$SELECTION$') ||  '%')  ORDER BY pg_class.relpages DESC;

Благодаря этим макросам можно оперативно оценить объем тех или иных объектов в базе.

Макросы в pgAdmin. Список таблиц и поля таблицы.

Я работаю программистом и мне часто приходится иметь дело с базами данных. Основная часть моих обязанностей на текущей работе -это разработка схемы БД для Postgresql и создание функций на языке pgSQL. Для разработки кода функций используется программа pgAdmin 3, поставляемая вместе с PostgreSQL. Программа бесплатна, но не всегда удобна. Особенно это касается задач, связанных с созданием структуры СУБД. Для этих целей лучше всего подходит PostgreSQL Maestro, чей дружелюбный интерфейс позволяет упростить и ускорить построение структуры базы.

Однако за долгое время работы с этими программами я заметил, что pgAdmin гораздо стабильней работает, чем PostgreSQL Maestro. Последняя в случае недоступности сервера БД может просто  завершиться с ошибкой, не давая возможности сохранить набранный текст. Другой проблемой этой программы является отображение не в той кодировке сообщений от сервера БД. Из-за этого часто невозможно узнать, в чем причина ошибки того или иного запроса. Поэтому для написания и отладки функций я использую родную программу pgAdmin.

Однако и при написании кода возникает потребность узнать точное наименование таблицы ее структуру. Раньше приходилось постоянно лазить дереву с таблицами и искать нужные названия полей. Это замедляло написание коды. Однако недавно я открыл для себя

замечательную функцию этой программы - макросы.

Макрос - это команда SQL, которая может запускаться по нажатию определенного сочетания клавиш в окне Query Tool.Макросы предназначены для выполнения каких-либо типовых операций.

Чтобы создать макрос, необходимо нажать на команду "Макрос" в главном меню Query Tool. 

В появившемся окне слева выбираем сочетание клавиш, а справа вставляем текст макроса, который будет соответствовать данному сочетанию клавиш. 

Макросу нужно задать имя, а затем нажать кнопку "Сохранить".

 Текст макроса представляет собой обычный запрос, за исключением специального ключевого слова $SELECTION$. В это слово подставляется выделенный при редактировании в Query Tool текст. 

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

А теперь приведу примеры полезных макросов, которые я использую в своей работе.

1.  Макрос, получающий список таблиц для схемы public базы данных PostgreSQL.
   
   
   SELECT * FROM information_schema.TABLES

   WHERE 
   table_type='BASE TABLE' AND table_schema='public'
   ORDER BY table_name




2.  Макрос, получающий список полей в таблице, чье название выделено (очень не хватало этой возможности, когда я перешел с MS SQL Server 2008, в Microsoft Management Studio она была по-умолчанию).
   
   

   SELECT quote_ident(nspname) || '.' || quote_ident(relname) AS table_name, 
          quote_ident(attname) AS field_name, 
          format_type(atttypid,atttypmod) AS field_type, 
          case when attnotnull then ' NOT NULL' else '' end AS null_constraint,
          case when atthasdef then 'DEFAULT ' || 
                                   ( SELECT pg_get_expr(adbin, attrelid) 
                                       FROM pg_attrdef 
                                      WHERE adrelid = attrelid AND adnum = attnum )::text else ''
          end AS dafault_value,
          case when nullif(confrelid, 0) IS NOT NULL
               then confrelid::regclass::text || '( ' || 
                    array_to_string( ARRAY( SELECT quote_ident( fa.attname ) 
                                              FROM pg_attribute AS fa 
                                             WHERE fa.attnum = ANY ( confkey ) 
                                               AND fa.attrelid = confrelid
                                             ORDER BY fa.attnum 
                                           ), ','
                                    ) || ' )'
               else '' end AS references_to
     FROM pg_attribute 
          LEFT OUTER JOIN pg_constraint ON conrelid = attrelid 
                                       AND attnum = conkey[1] 
                                       AND array_upper( conkey, 1 ) = 1,
          pg_class, 
          pg_namespace
    WHERE pg_class.oid = attrelid
      AND pg_namespace.oid = relnamespace
      AND relname = btrim( '$SELECTION$' )
      AND attnum > 0
      AND NOT attisdropped
    ORDER BY attrelid, attnum;

Таким образом, когда, в случае разработки, я забываю точное название таблицы, я выполняю команду для первого макроса. И у меня перед глазами появляется весь список таблиц схемы public. А когда мне хочется узнать список полей данной таблицы, то я просто выделяю ее название и запускаю второй макрос. Теперь разработка PgSql-функций для меня значительно ускорилась :)

Активация расширений Google Chrome по нажатию клавиш

Все больше открываю для себя замечательных возможностей браузера Google Chrome. Мне как программисту, освоившему метод слепой печати, не всегда нравится серфить в Интернете, используя только мышь. При этом, я часто сохраняю полезные и интересные заметки из Интернета с помощью сервиса www.evernote.com. В моем браузере установлен плагин Evernote Web Clipper, который позволяет выделенный на веб-странице текст или всю веб страницу отправить в мой блокнот для последующего ознакомления. Для запуска этого плагина я всегда использовал контекстное меню и не знал, что каждому установленном плагину в Chrome для запуска можно назначить функциональную клавишу. Это делается следующим образом:

1. Кликаем на пункт "Настройки" главного меню браузера
2. Слева будет один из трех пунктов - "Расширения". Кликаем по нему.
3. Пролистав список установленных расширений внизу справа Вы увидите пункт "Настроить команды"
4. Появится вот такое окошечко, где можно задать комбинации клавиш для активизации плагина (в данном случае комбинация Ctrl+E запускает плагин Evernote Web Clipper)

Этот способ позволил мне ускорить сохранение заметок в Evernote, и сделал еще более продуктивным процесс получения знаний посредством Интернет-серфинга.

Динамический PIVOT в Microsoft SQL Server

Необходимо из исходной таблицы получить сводную таблицу (см. рис.).  В качестве столбцов в новой таблице  будут годы, в строках названия клиентов, а в ячейках – суммы по полю [Count], соответствующие данному клиенту и году. Сложность заключается в том, что неизвестно количество лет, следовательно,  количество столбцов в новой таблице должно вычисляться динамически.

После некоторых размышлений и исследования возможностей Microsoft SQL Server  2008 R2 был разработан следующий код:

--Создаем тестовую таблицу


CREATE TABLE Table1


(


[Customer] VARCHAR(10),


[MONTH] INT,


[YEAR] INT,


[COUNT] INT


)


GO


--вставляем данные в таблицу


INSERT INTO Table1([Customer],[MONTH],[YEAR],[COUNT])


VALUES ('A',9,2011,1),


('A',9,2011,8),


('A',9,2012,1),


('B',9,2011,3),


('B',10,2012,2),


('B',10,2012,2)


GO


--создаем переменную для хранения строки с заголовками столбцов 


DECLARE @columns VARCHAR(8000)


SELECT @columns = COALESCE(@columns + ',[' + CAST([YEAR] AS VARCHAR) + ']', '[' + CAST([YEAR] AS VARCHAR)+ ']')


FROM    Table1


GROUP BY [YEAR]


DECLARE @query NVARCHAR(4000)


--динамически конструируем текст запроса


SET @query = 'select * from (select 


[customer],[Year],[Count] from Table1


)AS SourceTable


Pivot(sum([count]) for [Year] IN (' + @columns + ')) AS PVT;'


--выполнение запроса с помощью хранимой процедуры


EXECUTE SP_EXECUTESQL @query

Данный код создает таблицу Table1, соответствующую исходной таблице на рисунке. Затем 

переменной @COLUMNS присваивается строка с  годами через запятую, которые считываются запросом, с применением функции COALESCE. После этого можно сконструировать запрос, создающий сводную таблицу. Данный запрос в виде строки сохраняется в переменной @query и выполняется посредством хранимой процедуры sp_executesql. Результат выполнения запроса:

Как сохранить видеоролик с Youtube во время просмотра

При просмотре видеоролика на Youtube часто хочется его сохранить на компьютер не уходя с текущей страницы. При этом не возникает желания заходить на веб-сайты, типа http://ru.savefrom.net, которые предоставляют возможность загрузки роликов. Способ, который я опишу, подходит для пользователей браузера Mozilla Firefox.

• Шаг 1. Для начала вам понадобится зайти на страницу https://addons.mozilla.org/en-US/firefox/addon/greasemonkey и нажать кнопку "Add to Firefox". Расширение Greasemonkey, которое установится в ваш бразуре, позволяет дополнять различные сайты пользовательскими JavaScript-скриптами и выполнять эти скрипты в Firefox. Это дает возможность расширять функции сайтов, на которые настроены дополнительные скрипты.

• Шаг 2. Далее необходимо зайти на страницу http://userscripts.org/scripts/show/25105 и нажать кнопку "Install". После этого, разрешите установку дополнительного скрипта.
• Шаг 3. Проверка работы установленного дополнения. Зайдите на Youtube и убедитесь, что при просмотре видеоролика появилась кнопка "Скачать".