C++, SQL, SQLite

Аналог UNNEST в SQLite

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

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

[pastacode lang=»sql» manual=»with%20cte_code(id%2Ccode)%20as%20(%0Aselect%20rowid%2C%20a.*%20from%20%0A%09(values(‘111’)%2C(‘222’)%2C(‘333’))%20as%20a)%2C%0Acte_dates(id%2Cdate)%20as%20(%0Aselect%20rowid%2C%20b.*%20from%20%0A%09(values(‘2022-08-09’)%2C(‘2022-08-10’)%2C%0A%09%20(‘2022-08-11’))%20as%20b)%0A%0Ainsert%20into%20ones(task_id%2C%20code%2C%20date)%0Aselect%201%2C%20a.code%2C%20b.date%0Afrom%20cte_code%20a%20%0Ainner%20join%20cte_dates%20b%20on%20b.id%20%3D%20a.id» message=»Списки поотдельности» highlight=»1,4,9″ provider=»manual»/]

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

[pastacode lang=»sql» manual=»with%20cte_pairs(code%2C%20date)%20as%20(%0Aselect%20*%20from%20%0A%09(values(‘111’%2C%20’2022-08-09’)%2C%0A%09%20(‘222’%2C%20’2022-08-10’)%2C%0A%09%20(‘333’%2C%20’2022-08-11’)))%0A%0Ainsert%20into%20ones(task_id%2C%20code%2C%20date)%0Aselect%201%2C%20a.code%2C%20a.date%0Afrom%20cte_pairs%20a» message=»Спарованный список» highlight=»1″ provider=»manual»/]

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

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

[pastacode lang=»cpp» manual=»QStringList%20codes%2C%20dates%3B%0AQString%20qry%20%3D%20QString(%22with%20cte_code(id%2Ccode)%20as%20(%20%22%0A%09%09%09%09%09%20%20%22select%20rowid%2C%20a.*%20from%20%22%0A%09%09%09%09%09%20%20%22(values(‘%252’))%20as%20a)%2C%20%22%0A%09%09%09%09%09%20%20%22cte_dates(id%2Cdate)%20as%20(%20%22%0A%09%09%09%09%09%20%20%22select%20rowid%2C%20b.*%20%22%0A%09%09%09%09%09%20%20%22from%20(values(‘%251′))%20as%20b)%20%22%0A%09%09%09%09%09%20%20%0A%09%09%09%09%09%20%20%22insert%20into%20ones(task_id%2C%20code%2C%20date)%20%22%0A%09%09%09%09%09%20%20%22select%201%2C%20a.code%2C%20b.date%20%22%0A%09%09%09%09%09%20%20%22from%20cte_code%20a%20%22%0A%09%09%09%09%09%20%20%22inner%20join%20cte_dates%20b%20on%20b.id%20%3D%20a.id%22)%0A%09.arg(dates.join(%22’)%2C%20(‘%22))%0A%09.arg(codes.join(%22’)%2C%20(‘%22))%3B» message=»» highlight=»» provider=»manual»/]

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»

Базы данных

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 для решения данного вопроса:

[pastacode lang=»sql» manual=»CREATE%20TABLE%20Codes%20(%0Acode%20STRING%20(50)%20UNIQUE%20NOT%20NULL)%3B» message=»» highlight=»» provider=»manual»/]

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

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

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