C++, SQL, SQLite

Аналог UNNEST в SQLite

Такой удобной функции, как UNNEST, в SQLite нет (пссс, об UNNEST в PostgreSQL я писала вот здесь). А что использовать, если нам нужно элементы некоего массива/списка/контейнера впихнуть в один запрос вставки данных, например? Можно воспользоваться WITH и VALUES.

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

with cte_code(id,code) as (
select rowid, a.* from 
	(values('111'),('222'),('333')) as a),
cte_dates(id,date) as (
select rowid, b.* from 
	(values('2022-08-09'),('2022-08-10'),
	 ('2022-08-11')) as b)

insert into ones(task_id, code, date)
select 1, a.code, b.date
from cte_code a 
inner join cte_dates b on b.id = a.id
Списки поотдельности

cte_code — список кодов, cte_dates — список дат. task_id — некая константа.

with cte_pairs(code, date) as (
select * from 
	(values('111', '2022-08-09'),
	 ('222', '2022-08-10'),
	 ('333', '2022-08-11')))

insert into ones(task_id, code, date)
select 1, a.code, a.date
from cte_pairs a
Спарованный список

cte_pairs — оба наших списка в одном.

Строка запроса в Qt C++, учитывая наличие QStringList codes и QStringList dates, может выглядеть так:

QStringList codes, dates;
QString qry = QString("with cte_code(id,code) as ( "
					  "select rowid, a.* from "
					  "(values('%2')) as a), "
					  "cte_dates(id,date) as ( "
					  "select rowid, b.* "
					  "from (values('%1')) as b) "
					  
					  "insert into ones(task_id, code, date) "
					  "select 1, a.code, b.date "
					  "from cte_code a "
					  "inner join cte_dates b on b.id = a.id")
	.arg(dates.join("'), ('"))
	.arg(codes.join("'), ('"));
PostgreSQL

PostgreSQL: Запрос на получение списка комментариев к колонкам таблицы

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

Организовать это можно через комментарий к полю. Задать его можно через интерфейс pgAdmin, в свойствах поля:

Или прописать в скрипте:

COMMENT ON COLUMN public.products.product_id
    IS 'Комментарий';
Добавление комментария к колонке

Допустим, комментарии ко всем колонкам прописаны. Как их теперь получить запросом, чтоб красивенько вывести таблицу на форме?

select subq.attname::text, d.description
	FROM 
		(SELECT c.relname, obj_description(c.oid) obj_description, a.attname, c.oid, a.attnum
		FROM pg_class c, pg_attribute a
		WHERE c.oid = a.attrelid
		AND c.relname in ('products')
		AND a.attnum > 0) subq 
	LEFT OUTER JOIN pg_description d ON (d.objsubid = subq.attnum AND d.objoid = subq.oid)
	where subq.attname not ilike '%pg.dropped%';
Имена колонок таблицы products и комментарии к ним

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

 

PostgreSQL, Базы данных

PostgreSQL: Результаты запроса в JSON

Мне бы хотелось рассказать очень кратенько про две часто мною используемые функции — json_agg и row_to_json.

Первая используется для получения результата выполнения SQL запроса как массив объектов JSON. Таким образом, одна запись складывается в один объект массива:

select json_agg(t) from
(select * from products) t
json_agg
select json_agg(t) from
(select product_id, min_storage_temp, max_storage_temp 
 from products where coalesce(min_storage_temp,'') <> '') t
Получить данные указанных полей

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

[
{
«product_id»:»aaa5f25c-5734-11ec-8cd2-00155d999336″,
«min_storage_temp»:»-4″,
«max_storage_temp»:»+6″
},
{
«product_id»:»1735acd6-fd3f-4146-8efc-718d7c55faaf»,
«min_storage_temp»:»0″,
«max_storage_temp»:»+6″
},
{
«product_id»:»c89cc4d8-c531-11eb-958d-772a230b0b21″,
«min_storage_temp»:»-4″,
«max_storage_temp»:»+6″
}
]

Для получения результата запроса, возвращающего одну строку, в качестве объекта JSON можно воспользоваться функцией row_to_json:

select row_to_json(t) FROM 
(select * from products where product_id = '123') t
row_to_json

[
{
«product_id»:»c89cc4d8-c531-11eb-958d-772a230b0b21″,
«min_storage_temp»:»-4″,
«max_storage_temp»:»+6″
}
]

PostgreSQL, SQL

Прервать соединения IP с базой данных в PostgreSQL

В сообщении Принудительное удаление базы данных в PostgreSQL я писала о том, как принудительно закрыть все соединения с базой данных. Это удобно, когда есть «висящие» соединения, которые никак не хотят завершаться. Или нужно срочно удалить базу данных и нет времени выяснять, кто забыл от нее отключиться 🙂 Сегодня я хочу показать, как прервать соединения IP с базой данных.

Сначала выведем количество подключений с каждого IP к базе данных: Продолжить чтение «Прервать соединения IP с базой данных в PostgreSQL»

PostgreSQL, Qt, Портфолио

DBServerWizard — автоматизация работы с базами данных PostgreSQL

Сегодня хочу познакомить вас с утилитой DBServerWizard, написание которой закончила намедни. Если коротко, ее предназначение — самые базовые элементы управления базами данных PostgreSQL.

МенюУстановка сервераРезервное копирование/ восстановление из копииСоздание базы данных
Меню
Установка сервера
Бэкап/рестор
Создание БД

А если расписать немного подробнее, то перечень такой:

  • Установка сервера PostgreSQL (включает предварительную проверку наличия уже установленного сервера).
  • Создание и удаление баз данных. Есть некоторый обязательный набор баз данных; при подключении к серверу происходит проверка наличия этого минимального набора, если какие-либо БД из этого списка отсутствуют, то предлагается произвести создание БД и восстановление из бэкапа. Доступно принудительное удаление при наличии «застрявших» подключений, мешающих операции.
  • Резервное копирование и восстановление из копии. Перед восстановлением базы данных производится резервное копирование «на всякий случай».
  • Обновление баз данных из скриптов. Скрипт разделяется на отдельные инструкции, которые выполняются последовательно. Невыполненные инструкции записываются в отдельный файл.

Следующие сообщения были написаны в течение работы над утилитой:

Qt: Восстановление pg_restore БД PostgreSQL в Windows

Принудительное удаление базы данных в PostgreSQL

Qt: Резервная копия pg_dump БД PostgreSQL в Windows

Файл паролей PostgreSQL pgpass в Windows

Путь к каталогу bin PostgreSQL Windows

Чтобы заказать проект или получить консультацию, перейдите, пожалуйста, на страницу Контакты.

Моё портфолио

Инфо обо мне

PostgreSQL, Qt

Qt: Восстановление pg_restore БД PostgreSQL в Windows

Здесь: Qt: Резервная копия pg_dump БД PostgreSQL в Windows я показала один из способов организации создания резервной копии базы данных.

Сейчас речь пойдет о восстановлении базы из бэкапа. Здесь тоже будем использовать пакетный файл для вызова pg_restore.

Про каталог bin, файл паролей pgpass написано в статье по ссылке выше.

//name - имя базы данных
//file - файл резервной копии
void myClass::restoreDatabase(QString name, QString file)
{
    QString errorMsg;
    QString connectionName = connectToDB(name, errorMsg);
    if (connectionName.isEmpty()){
        error(errorMsg);
        return;
    }
    QString binDir = postgresBinDirectory();
    if (binDir.isEmpty()) {
        error(tr("Системный каталог bin сервера не найден. Операция "
                 "восстановления базы данных %1 из резервной копии %2 прервана")
              .arg(name).arg(file));
        QSqlDatabase::removeDatabase(connectionName);
        return;
    }
    QSqlQuery query(QSqlDatabase::database(connectionName));
	//сначала удаляем схему (у меня паблик), 
	//затем ее снова создаем - чистенькую
    if (!query.exec(QString("DROP SCHEMA public cascade;  "
                            "CREATE SCHEMA public "
                            "AUTHORIZATION postgres; "
                            "COMMENT ON SCHEMA public "
                            "IS 'standard public schema'; "
                            "GRANT ALL ON SCHEMA public TO PUBLIC; "
                            "GRANT ALL ON SCHEMA public TO %1;")
                    .arg(postgresUser))) {
        error(tr("Ошибка удаления схемы базы данных %1: %2")
              .arg(name).arg(query.lastError().text()));
        QSqlDatabase::removeDatabase(connectionName);
        return;
    }
    QSqlDatabase::removeDatabase(connectionName);
	//текст batch-файла
    QString batText = QString("@ECHO OFF \n"
                              "SET dmpfile=%1 \n"
                              "set text = Begin to restore...  \n"
                              "echo %text% \n"
                              "cd /d %2 \n"
                              "pg_restore --host=%4 --port=%5 "
                              "--username=%6 --verbose -d "
                              " %3 \"%dmpfile%\"\n"
                              "set /p id=\"The process is finished successfully. "
                              "Press Enter to exit...\"")
            .arg(file, binDir, name,
                 host, ui->port->text(), postgresUser);
    QString fileName = QString("%1-restore.bat")
            .arg(name);
    if (!writeBatFile(batText, fileName, 
					  QCoreApplication::applicationDirPath()+"/temp"))
        return;
	//обновляем файл паролей, если нужно
    updatePgPassFile(QString("%1:%2:%3:%4:%5")
                     .arg(host,
                          ui->port->text(),
                          name,
                          postgresUser,
                          ui->password->text()));
	//вызываем пакетный файл для выполнения восстановления базы данных
    system(qPrintable("cmd.exe /c \""+
                      QDir::toNativeSeparators(
                          QString("%1/%2")
                          .arg(QCoreApplication::applicationDirPath()+"/temp")
                          .arg(fileName)+"\"")));
}

 

PostgreSQL

Принудительное удаление базы данных в PostgreSQL

Удалить базу данных, не обращая внимание на текущие сессии? Вопрос решается в три запроса!

1. Предотвращаем дальнейшие подключения к базе данных dbase_name:

update pg_database set datallowconn = 'false' 
where datname = 'dbase_name';

2. Закрываем все текущие сессии:

select pg_terminate_backend(pg_stat_activity.pid)
from pg_stat_activity
where pg_stat_activity.datname = 'dbase_name' and 
pid <> pg_backend_pid();

3. Собственно, удаляем базу данных:

drop database dbase_name;

Благодарность отправляется в Хабр Q&A.

PostgreSQL, Qt, Базы данных

Qt: Резервная копия pg_dump БД PostgreSQL в Windows

Пример того, как можно реализовать резервное копирование базы данных PostgreSQL в Qt. Создается batch-файл, где вызывается утилита pg_dump.

//QString name - имя базы данных для создания копии
//file - "куда складывать" бэкап
bool myClass::backupDatabase(QString name, QString file)
{
    QString binDir = postgresBinDirectory();
    if (binDir.isEmpty()) {
        error(tr("Системный каталог bin сервера не найден. "
				 "Операция восстановления базы данных "
				 "%1 из резервной копии %2 прервана")
              .arg(name).arg(file));
        return false;
    }
    if (file.isEmpty()) {
        QDir dir(QCoreApplication::applicationDirPath()+
				 "/db_dumps/");
        if (!dir.exists())
            dir.mkpath(QCoreApplication::applicationDirPath()+
					   "/db_dumps/");
        file = QString("%1/%2-%3.dump")
                .arg(QCoreApplication::applicationDirPath()+
					 "/db_dumps")
                .arg(name)
                .arg(QDateTime::currentDateTime()
					 .toString("yyyyMMddhhmm"));
    }
    QString text = QString(
		"@ECHO OFF \n"
        "cd /d %1 \n"
        "pg_dump -Fc -U %4 -Z 9 -v %2 > \"%3\" \n"
        "set /p id=\"Press Enter to exit...\"\n")
            .arg(binDir)
            .arg(name)
            .arg(file)
            .arg(postgresUser);
    QString fileName = QString("%1-dump.bat")
            .arg(name);
	//запись Значения строки text в файл *.bat
    if (!writeBatFile(text, fileName, 
				 QCoreApplication::applicationDirPath()+"/temp"))
		return;
	//обновление файла паролей pgpass
    updatePgPassFile(QString("%1:%2:%3:%4:%5")
                     .arg(host,
                          ui->port->text(),
                          name,
                          postgresUser,
                          ui->password->text()));
	//вызов созданного batch файла
    system(qPrintable("cmd.exe /c \""+
                      QDir::toNativeSeparators(
                          QString("%1/%2")
                          .arg(QCoreApplication::applicationDirPath()+
							   "/temp")
                          .arg(fileName)+"\"")));
	//если файл бэкапа имеет ненулевой размер
    return QFileInfo(file).size() > 0;
}

Как узнать путь к каталогу bin сервера PostgreSQL, подробно написано здесь: Путь к каталогу bin PostgreSQL Windows. При условии известности имени пользователя и его пароля можно запустить утилиту pg_dump без необходимости авторизации пользователем при наличии соответствующей строки подключения в файле паролей pgpass, про что подробно писала в этой статье: Файл паролей PostgreSQL pgpass в Windows. В вышеприведенном коде используется функция updatePgPassFile(QString), код которой можно найти по обозначенной ссылке.

Более ранняя статья на схожую тему: Резервное копирование базы данных по расписанию: MS SQL Server и PostgreSQL

PostgreSQL, Qt, Базы данных

Файл паролей PostgreSQL pgpass в Windows

Для восстановления базы данных из резервной копии используется исполняемый файл сервера pg_restore. Если в настройках сервера выставлена авторизация по имени и паролю, то при использовании утилиты необходимо вводить пароль при каждой операции восстановления БД из бэкапа. Что не подходит в случае пакетной операции, например, или когда операция должна производится без взаимодействия с пользователем в этом ключе. Тогда сервер использует файл паролей pgpass. Он может и отсутствовать. Что тогда делать? Создать и обновлять. Продолжить чтение «Файл паролей PostgreSQL pgpass в Windows»

PostgreSQL, Qt, Базы данных

Путь к каталогу bin PostgreSQL Windows

Задача: получить значение пути к каталогу бинарников сервера — к каталогу bin. Путь к каталогу data, как и прочие значения параметров времени выполнения, найти совершенно несложно: достаточно выполнить запрос show, воспользоваться функцией current_setting() или обратиться к представлению pg_settings:

show data_directory;
select current_setting('data_directory');
select setting from pg_settings 
   where name = 'data_directory';
Три равнозначных примера получения значения пути к каталогу data

Еще парочку примеров показывала вот в этой записи: Количество подключений к БД PostgreSQL.

С значением пути к каталогу bin не все так просто — запросом его не получишь. Но можно узнать из реестра. В реестре не хранится чистый ключ со значением пути к каталогу, к сожалению, но можно узнать путь к исполняемому файлу службы сервера.

Итак, интересующий нас ключ: Продолжить чтение «Путь к каталогу bin PostgreSQL Windows»