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("'), ('"));
Microsoft SQL Server, Qt

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

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

QSqlQuery query;
if (!query.exec("delete from people where age < 20")) {
	qDebug() << "error:" << query.lastError()->text();
	return;
}
qDebug() << "rows count" << query.numRowsAffected();
numRowsAffected()
QSqlQuery query;
if (!query.exec("select * from people where age < 35")) {
	qDebug() << "error:" << query.lastError()->text();
	return;
}
qDebug() << "rows count" << query.size();
size()

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

qDebug() << database.driver()->hasFeature(QSqlDriver::QuerySize);

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

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

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

Базы данных

SQLite, SQLiteStudio и отображение больших чисел в поле типа string в виде числа с плавающей точкой

В SQLite предусмотрено только 5 типов данных:

  • NULL. Пустое значение в таблице базы.
  • INTEGER. Целочисленное значение.
  • REAL. Числовое значение с плавающей точкой.
  • TEXT. Значение строки текста. Хранится с использованием кодировки базы данных (UTF-8, UTF-16BE или UTF-16LE).
  • BLOB. Значение бинарных данных, хранящихся точно в том же виде, в каком были введены.

Однако некоторые менеджеры, позволяющие управлять базами данных SQLite, предоставляют нам возможность «большего» выбора. Так, SQLite Studio предоставляют выбор из следующих типов данных:

  • bigint
  • blob
  • boolean
  • char
  • date
  • datetime
  • decimal
  • double
  • integer
  • int
  • none
  • numeric
  • real
  • string
  • text
  • time
  • varchar

Выходит, 17 против стандартных 5. Однако все эти 17 типов в конечном итоге приводятся все к тем же null, integer, real, text или blob. И каким образом после преобразования будут выглядеть данные решает SQLite, а не вы.

Например, в SQLite нет типа данных для хранения даты и времени. Однако есть встроенные функции SQLite для работы с этими типами данных. Для наглядности при создании столбца в разделе тип данных вы указываете datetime, но на самом деле данные будут храниться как тип данных text.

Итак, перейдем к теме. Задача: хранение кодов, состоящий из количества цифр >20, в поле базы строкового типа. Допустим, выбран тип string в SQLite Studio для решения данного вопроса:

CREATE TABLE Codes (
code STRING (50) UNIQUE NOT NULL);

Для строк 2222223333333333666666666999999999 и 1111111114444444444222222222333333333 в таблице вы увидете

Так, конечно же, не пойдет: код нужен целиком. Чтобы такие строки отображались корректно, необходимо использовать  тип данных text, char, varchar (которые в конечном итоге приводятся к типу данных text):

Дело в том, что тип string будет приводится к типам real или integer, если данные похожи на вещественное или целое число, соответственно. Так, строка вида ‘0147’ преобразуется в ‘147’.