Microsoft SQL Server, PostgreSQL, Qt, SQL, Базы данных

Лучшие записи 2019 года

Продолжим традицию: ниже темы, имеющие наилучшую статистику посещений за 2019 год. Пост за прошлый год: Лучшие записи за 2018 год

  1. Основы баз данных. ER-модель (сущность-связь)
  2. Добавление данных в таблицу QTableWidget. Очистка таблицы
  3. Функция, возвращающая таблицу returns table — в PostgreSQL и MS SQL Server
  4. PostgreSQL: unnest(array)
  5. PostgreSQL: Размер таблиц и базы данных
  6. PostgreSQL: Узнать данные о колонках таблицы
  7. Получить значения полей выделенной строки QTableView
  8. Qt. Динамическое создание виджетов на форме
  9. Триггеры в PostgreSQL
  10. Подключение к базе данных в Qt
Microsoft SQL Server, PostgreSQL, SQL

PostgreSQL и MSSQLServer: Ограничение количества отображаемых строк выборки

Вывести первые 100 строк запроса:

[pastacode lang=»sql» manual=»select%20*%20from%20table_name%0Aorder%20by%20id%0Alimit%20100″ message=»PostgreSQL limit» highlight=»3″ provider=»manual»/]

[pastacode lang=»sql» manual=»select%20*%20from%20table_name%20%0Afetch%20next%20100%20rows%20only» message=»PostgreSQL fetch» highlight=»2″ provider=»manual»/]

[pastacode lang=»sql» manual=»select%20*%20from%20table_name%0Aorder%20by%20id%0Aoffset%200%20rows%20fetch%20next%20100%20rows%20only» message=»Microsoft SQL Server offset…fetch» highlight=»3″ provider=»manual»/]

Вывести 100 строк выборки, пропустив 20 строк:

[pastacode lang=»sql» manual=»select%20*%20from%20table_name%0Aorder%20by%20id%0Alimit%20100%20offset%2020″ message=»PostgreSQL limit…offset» highlight=»3″ provider=»manual»/]

[pastacode lang=»sql» manual=»select%20*%20from%20table_name%20%0Aoffset%2020%20rows%20fetch%20next%20100%20rows%20only» message=»PostgreSQL offset…fetch» highlight=»2″ provider=»manual»/]

[pastacode lang=»sql» manual=»select%20*%20from%20table_name%0Aorder%20by%20id%0Aoffset%2020%20rows%20fetch%20next%20100%20rows%20only» message=»Microsoft SQL Server offset…fetch» highlight=»3″ provider=»manual»/]

Стоит отметить, что для использования предложения OFFSET и FETCH в MSSQLServer требуется ORDER BY. В PostgreSQL — нет. В MS SQL Server использование offset без fetch запрещено. В PostgreSQL — нет.

Для ограничения количества выводимых строк без сдвига для Microsoft SQL Server можно воспользоваться оператором top:

[pastacode lang=»sql» manual=»select%20top%20100%20*%0Afrom%20table_name%0Aorder%20by%20id» message=»Microsoft SQL Server top» highlight=»1″ provider=»manual»/]

Документация: PostgreSQL, MSSQLServer (offset…fetch, top).

Microsoft SQL Server, PostgreSQL, Qt, Базы данных

Резервное копирование базы данных по расписанию: MS SQL Server и PostgreSQL

Резервное копирование базы данных по расписанию: MS SQL Server и PostgreSQLНе во всех СУБД есть возможность настроить Резервное копирование базы данных по расписанию штатными средствами. Например, в Microsoft SQL Server это делается без проблем, а вот в PostgreSQL такой возможности «из коробки» нет.

Но выход есть: создать файл сценария и добавить его исполнение в расписание задач в Windows.

В переменных database, host, port, userName, bin, backupDir будут храниться значения имени базы данных, сервера, порта, имени пользователя БД, путь к bin папке PostgreSQL и директории сохранения резервных копий соответственно.

Пусть значение driver = 0 будет отвечать за PostgreSQL, а 1 — за Microsoft SQL Server.

Определим текст *.bat-файла:

[pastacode lang=»cpp» manual=»QString%20batText%20%3D%20%22%40ECHO%20OFF%20%5Cn%22%0A%20%20%20%20%20%20%20%20%22SET%20dmpfile%3D%22%2Bdatabase%2B%22-FullBackup-%25DATE%3A~6%2C4%25-%25DATE%3A~3%2C2%25-%25DATE%3A~0%2C2%25%20%5Cn%22%0A%20%20%20%20%20%20%20%20%22IF%20%5C%22%25TIME%3A~0%2C1%25%5C%22%3D%3D%5C%22%20%5C%22%20(SET%20dmpfile%3D%25dmpfile%25-0%25TIME%3A~1%2C1%25)%20ELSE%20(SET%20dmpfile%3D%25dmpfile%25-%25TIME%3A~0%2C2%25)%20%5Cn%22%0A%20%20%20%20%20%20%20%20%22SET%20dmpfile%3D%25dmpfile%25-%25TIME%3A~3%2C2%25-%25TIME%3A~6%2C2%25.backup%20%5Cn%22%0A%20%20%20%20%20%20%20%20%22set%20text%20%3D%20Begin%20to%20backup…%20%5Cn%22%0A%20%20%20%20%20%20%20%20%22echo%20%25text%25%20%5Cn%22%3B%0Aif%20(driver%3D%3D0)%0A%20%20%20%20%20%20%20batText%20%2B%3D%20QString(%0A%20%20%20%20%20%20%20%20%20%20%20%22cd%20%251%5C%20%5Cn%22%0A%20%20%20%20%20%20%20%20%20%20%20%22pg_dump.exe%20—host%20%252%20—port%20%253%20—username%20%254%20—no-password%20—format%22%20%0A%09%09%20%20%20%22custom%20—blobs%20—verbose%20—file%20%5C%22%255%5C%5C%25dmpfile%25%5C%22%20%256%22)%0A%20%20%20%20%20%20%20%20%20%20%20%20.arg(bin%2C%20host%2C%20port%2C%20userName%2C%20%0A%09%09%09%09%20backupDir.replace(%22%2F%22%2C%20%22%5C%5C%22)%2C%20database)%3B%0A%20else%0A%20%20%20%20%20%20%20batText%20%2B%3D%20QString(%22SQLCMD%20-S%20%251%20-E%20-Q%20%5C%22BACKUP%20DATABASE%20%252%20%22%0A%20%20%20%20%20%20%20%20%20%20%20%22TO%20DISK%20%3D%20’%253%2F%25dmpfile%25’%20%22%0A%20%20%20%20%20%20%20%20%20%20%20%22WITH%20INIT%2C%20NOFORMAT%2C%20SKIP%2C%20NOUNLOAD%5C%22%22)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20.arg(host%2C%20database%2C%20backupDir)%3B» message=»» highlight=»» provider=»manual»/]

Имя файла резервной копии будет иметь при этом подобный вид: DatabaseName-FullBackup-2018-08-07 -11-23-38.backup, то есть имя базы данных, FullBackup + таймстемп.

Далее запишем текст в файл: Продолжить чтение «Резервное копирование базы данных по расписанию: MS SQL Server и PostgreSQL»

Microsoft SQL Server, PostgreSQL, Базы данных

Триггер на групповую вставку в таблицу в Microsoft SQL Server

Многие СУБД (например, MySQL, PostgreSQL и т.д.) умеют правильно обрабатывать события по групповой вставке данных в таблицы. Но не все. Например, триггер на групповую вставку в таблицу в Microsoft SQl Server будет отличаться. Рассмотрим примеры запросов:

[pastacode lang=»sql» manual=»insert%20into%20people%0Aselect%20name%2C%20surname%2C%20age%20from%20portfolio%3B» message=»» highlight=»» provider=»manual»/]

Или такой:

[pastacode lang=»sql» manual=»insert%20into%20people(name%2C%20surname%2C%20age)%20values%0A(‘John’%2C%20’Smith’%2C%2012)%2C%0A(‘Ann’%2C%20’Black’%2C%2029)%2C%0A(‘Samantha’%2C%20’Doeson’%2C%2038)%3B» message=»» highlight=»» provider=»manual»/]

Продолжить чтение «Триггер на групповую вставку в таблицу в Microsoft SQL Server»

Microsoft SQL Server, Qt

QSqlQuery::numRowsAffected(), QSqlQuery::size() и MS SQL Server

Для получения количества обработанных записей запросом (insert, update, delete) в Qt используют функцию numRowsAffected(), а для получения размера выборки — size() объекта QSqlQuery:

[pastacode lang=»cpp» manual=»QSqlQuery%20query%3B%0Aif%20(!query.exec(%22delete%20from%20people%20where%20age%20%3C%2020%22))%20%7B%0A%09qDebug()%20%3C%3C%20%22error%3A%22%20%3C%3C%20query.lastError()-%3Etext()%3B%0A%09return%3B%0A%7D%0AqDebug()%20%3C%3C%20%22rows%20count%22%20%3C%3C%20query.numRowsAffected()%3B» message=»numRowsAffected()» highlight=»6″ provider=»manual»/]

[pastacode lang=»cpp» manual=»QSqlQuery%20query%3B%0Aif%20(!query.exec(%22select%20*%20from%20people%20where%20age%20%3C%2035%22))%20%7B%0A%09qDebug()%20%3C%3C%20%22error%3A%22%20%3C%3C%20query.lastError()-%3Etext()%3B%0A%09return%3B%0A%7D%0AqDebug()%20%3C%3C%20%22rows%20count%22%20%3C%3C%20query.size()%3B» message=»size()» highlight=»6″ provider=»manual»/]

Но не все СУБД поддерживают эту функцию. Например, SQLite и, о Боже! — Microsoft SQL Server (как не стыдно!). Проверить поддержку данной функции (а заодно и функции QSqlQuery::size()) можно так:

[pastacode lang=»cpp» manual=»qDebug()%20%3C%3C%20database.driver()-%3EhasFeature(QSqlDriver%3A%3AQuerySize)%3B» message=»» highlight=»» provider=»manual»/]

где database — объект подключения к базе данных QSqlDatabase. Если в выводе приложения вы увидите false, то прогноз пессимистичен.

Что делать в этом случае?

Продолжить чтение «QSqlQuery::numRowsAffected(), QSqlQuery::size() и MS SQL Server»

Microsoft SQL Server, Qt, Базы данных

Подключение к базе данных MS SQL Server в Qt

В записи Подключение к базе данных в Qt на примере СУБД PostgreSQL я показала, как произвести подключение к базе данных. С MS SQL Server немного запутанней получается (не привыкать).

MS SQL Server может использовать 2 вида аутентификации: аутентификация windows и аутентификация  SQL Server. Ниже рассмотрим оба способа подключения. Продолжить чтение «Подключение к базе данных MS SQL Server в Qt»

Microsoft SQL Server

Microsoft SQL Server: Удалить MS_Description для всех колонок таблиц

Потребовалось мигрировать с PostgreSQL в Microsoft SQL Server. И я, доверчивая душа, воспользовалась программой ESF Migration tool kit. Полную версию покупать не стала — мне же на один раз только надо. А эта чудо-программа добавила в описании ко всем полям всех таблиц описание «TRIAL». Не смертельно, но и неприятно. К тому же все триггеры и функции все равно пришлось самостоятельно писать. Так вот. Программисты — люди ленивые, поэтому написала процедуру, которая сама уберет все описания к полям. Использует она системную процедуру sp_dropextendedproperty:

[pastacode lang=»sql» manual=»declare%20%40table%20nvarchar(100)%2C%20%40column%20nvarchar(100)%3B%0Adeclare%20cur%20cursor%20local%20scroll%0Afor%20select%20A.table_name%2C%20A.column_name%0Afrom%20information_schema.columns%20A%0Ainner%20join%20information_schema.tables%20B%20%0Aon(A.table_name%20%3D%20B.table_name)%0Awhere%20A.table_schema%20%3D%20’dbo’%20and%20%0AA.table_catalog%20%3D%20’database_name’%20and%20%0A—%D1%82%D0%BE%D0%BB%D1%8C%D0%BA%D0%BE%20%D0%B4%D0%BB%D1%8F%20%D1%82%D0%B0%D0%B1%D0%BB%D0%B8%D1%86%2C%20%D0%BD%D0%B5%20%D1%82%D1%80%D0%BE%D0%B3%D0%B0%D1%8F%20%D0%BF%D1%80%D0%B5%D0%B4%D1%81%D1%82%D0%B0%D0%B2%D0%BB%D0%B5%D0%BD%D0%B8%D1%8F%0AB.table_type%3D’base%20table’%3B%0A%0Aopen%20cur%3B%0Afetch%20first%20from%20cur%20into%20%40table%2C%20%40column%3B%0Awhile(%40%40fetch_status%20%3D%200)%20begin%0A%09EXEC%20sp_dropextendedproperty%20’MS_Description’%2C%20%0A%09%09N’schema’%2C%20N’dbo’%2C%20N’table’%2C%20%40table%2C%20%0A%09%09N’column’%2C%20%40column%3B%0A%20%20%20%20fetch%20next%20from%20cur%20into%20%40table%2C%20%40column%3B%0A%20%20%20%20end%3B%0Aclose%20cur%3B%0Adeallocate%20cur%3B%0AEND» message=»Курсор для удаления описания к полям таблиц» highlight=»15″ provider=»manual»/]

Пример, приведенный на странице форума MSDN, почему-то не сработал — все описания остались на прежних местах.

Microsoft SQL Server, PostgreSQL

Функция, возвращающая таблицу returns table — в PostgreSQL и MS SQL Server

Хочу сегодня привести примеры создания функций для СУБД Microsoft SQL Server и PostgreSQL. Функция возвращает таблицу с 3-мя полями.

Как это пишется в MS SQL Server:

CREATE FUNCTION dbo.myFunction (--variables list--)
RETURNS result_table TABLE(field1 integer, field2 integer, field3 integer) 
AS
BEGIN
	insert into result_table 
	select val1, val2, val3 from another_table;
	return;
END;

А вот так в PostgreSQL:

CREATE OR REPLACE FUNCTION public.get_count_in_batch(--variables list--)
    RETURNS TABLE(field1 integer, field2 integer, field3 integer) 
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
    ROWS 1000
AS $BODY$
begin
	return query select val1, val2, val3 from another_table;
end;
$BODY$;

Конечно, здесь примеры сокращены до минимума, только лишь с целью показать механизм возврата результата. Продолжить чтение «Функция, возвращающая таблицу returns table — в PostgreSQL и MS SQL Server»