Lightweight 0.20251202.0
Loading...
Searching...
No Matches
PostgreSqlFormatter.hpp
1// SPDX-License-Identifier: Apache-2.0
2#pragma once
3
4#include "../SqlQueryFormatter.hpp"
5#include "SQLiteFormatter.hpp"
6
7#include <reflection-cpp/reflection.hpp>
8
9#include <format>
10
11namespace Lightweight
12{
13
14class PostgreSqlFormatter final: public SQLiteQueryFormatter
15{
16 public:
17 using SQLiteQueryFormatter::CreateTable;
18
19 [[nodiscard]] StringList DropTable(std::string_view schemaName,
20 std::string_view const& tableName,
21 bool ifExists = false,
22 bool cascade = false) const override
23 {
24 std::string sql = ifExists ? std::format("DROP TABLE IF EXISTS {}", FormatTableName(schemaName, tableName))
25 : std::format("DROP TABLE {}", FormatTableName(schemaName, tableName));
26 if (cascade)
27 sql += " CASCADE";
28 sql += ";";
29 return { sql };
30 }
31
32 [[nodiscard]] std::string BinaryLiteral(std::span<uint8_t const> data) const override
33 {
34 std::string result;
35 result.reserve((data.size() * 2) + 4);
36 result += "'\\x";
37 for (uint8_t byte: data)
38 result += std::format("{:02X}", byte);
39 result += "'";
40 return result;
41 }
42
43 [[nodiscard]] std::string QueryLastInsertId(std::string_view /*tableName*/) const override
44 {
45 // NB: Find a better way to do this on the given table.
46 // In our case it works, because we're expected to call this right after an insert.
47 // But a race condition may still happen if another client inserts a row at the same time too.
48 return std::format("SELECT lastval();");
49 }
50
51 [[nodiscard]] std::string_view DateFunction() const noexcept override
52 {
53 return "CURRENT_DATE";
54 }
55
56 [[nodiscard]] std::string BuildColumnDefinition(SqlColumnDeclaration const& column) const override
57 {
58 std::stringstream sqlQueryString;
59
60 sqlQueryString << '"' << column.name << "\" ";
61
62 // Detect PostgreSQL auto-increment columns by checking for nextval() in default value.
63 // This handles restore of backed-up tables where SERIAL columns have their default
64 // value captured as nextval('"TableName_id_seq"'::regclass).
65 bool const isAutoIncrementViaDefault = column.defaultValue.find("nextval(") != std::string::npos;
66 bool const isAutoIncrement = column.primaryKey == SqlPrimaryKeyType::AUTO_INCREMENT || isAutoIncrementViaDefault;
67
68 if (isAutoIncrement)
69 sqlQueryString << "SERIAL";
70 else
71 sqlQueryString << ColumnType(column.type);
72
73 if (column.required)
74 sqlQueryString << " NOT NULL";
75
76 // Only add inline PRIMARY KEY for explicitly marked AUTO_INCREMENT columns.
77 // For columns detected via nextval() default, the table-level PRIMARY KEY constraint
78 // will handle it to avoid "multiple primary keys" error.
79 if (column.primaryKey == SqlPrimaryKeyType::AUTO_INCREMENT)
80 sqlQueryString << " PRIMARY KEY";
81 else if (column.primaryKey == SqlPrimaryKeyType::NONE && !column.index && column.unique)
82 sqlQueryString << " UNIQUE";
83
84 // Don't output default value for auto-increment columns as SERIAL handles it
85 if (!column.defaultValue.empty() && !isAutoIncrement)
86 sqlQueryString << " DEFAULT " << column.defaultValue;
87
88 return sqlQueryString.str();
89 }
90
91 [[nodiscard]] std::string ColumnType(SqlColumnTypeDefinition const& type) const override
92 {
93 using namespace SqlColumnTypeDefinitions;
94
95 // PostgreSQL stores all strings as UTF-8
96 return std::visit(detail::overloaded {
97 [](Bigint const&) -> std::string { return "BIGINT"; },
98 [](Binary const& type) -> std::string { return std::format("BYTEA", type.size); },
99 [](Bool const&) -> std::string { return "BOOLEAN"; },
100 [](Char const& type) -> std::string { return std::format("CHAR({})", type.size); },
101 [](Date const&) -> std::string { return "DATE"; },
102 [](DateTime const&) -> std::string { return "TIMESTAMP"; },
103 [](Decimal const& type) -> std::string {
104 return std::format("DECIMAL({}, {})", type.precision, type.scale);
105 },
106 [](Guid const&) -> std::string { return "UUID"; },
107 [](Integer const&) -> std::string { return "INTEGER"; },
108 [](NChar const& type) -> std::string { return std::format("CHAR({})", type.size); },
109 [](NVarchar const& type) -> std::string {
110 if (type.size == 0)
111 return "TEXT";
112 return std::format("VARCHAR({})", type.size);
113 },
114 [](Real const&) -> std::string { return "REAL"; },
115 [](Smallint const&) -> std::string { return "SMALLINT"; },
116 [](Text const&) -> std::string { return "TEXT"; },
117 [](Time const&) -> std::string { return "TIME"; },
118 [](Timestamp const&) -> std::string { return "TIMESTAMP"; },
119 // NB: PostgreSQL doesn't have a TINYINT type, but it does have a SMALLINT type.
120 [](Tinyint const&) -> std::string { return "SMALLINT"; },
121 [](VarBinary const& /*type*/) -> std::string { return std::format("BYTEA"); },
122 [](Varchar const& type) -> std::string {
123 if (type.size == 0)
124 return "TEXT";
125 return std::format("VARCHAR({})", type.size);
126 },
127 },
128 type);
129 }
130
131 // NOLINTNEXTLINE(readability-function-cognitive-complexity)
132 [[nodiscard]] StringList AlterTable(std::string_view schemaName,
133 std::string_view tableName,
134 std::vector<SqlAlterTableCommand> const& commands) const override
135 {
136 std::stringstream sqlQueryString;
137
138 int currentCommand = 0;
139 for (SqlAlterTableCommand const& command: commands)
140 {
141 if (currentCommand > 0)
142 sqlQueryString << '\n';
143 ++currentCommand;
144
145 using namespace SqlAlterTableCommands;
146 sqlQueryString << std::visit(
147 detail::overloaded {
148 [schemaName, tableName](RenameTable const& actualCommand) -> std::string {
149 return std::format(R"(ALTER TABLE {} RENAME TO "{}";)",
150 FormatTableName(schemaName, tableName),
151 actualCommand.newTableName);
152 },
153 [schemaName, tableName, this](AddColumn const& actualCommand) -> std::string {
154 return std::format(R"(ALTER TABLE {} ADD COLUMN "{}" {} {};)",
155 FormatTableName(schemaName, tableName),
156 actualCommand.columnName,
157 ColumnType(actualCommand.columnType),
158 actualCommand.nullable == SqlNullable::NotNull ? "NOT NULL" : "NULL");
159 },
160 [schemaName, tableName, this](AlterColumn const& actualCommand) -> std::string {
161 return std::format(
162 R"(ALTER TABLE {0} ALTER COLUMN "{1}" TYPE {2}, ALTER COLUMN "{1}" {3} NOT NULL;)",
163 FormatTableName(schemaName, tableName),
164 actualCommand.columnName,
165 ColumnType(actualCommand.columnType),
166 actualCommand.nullable == SqlNullable::NotNull ? "SET" : "DROP");
167 },
168 [schemaName, tableName](RenameColumn const& actualCommand) -> std::string {
169 return std::format(R"(ALTER TABLE {} RENAME COLUMN "{}" TO "{}";)",
170 FormatTableName(schemaName, tableName),
171 actualCommand.oldColumnName,
172 actualCommand.newColumnName);
173 },
174 [schemaName, tableName](DropColumn const& actualCommand) -> std::string {
175 return std::format(R"(ALTER TABLE {} DROP COLUMN "{}";)",
176 FormatTableName(schemaName, tableName),
177 actualCommand.columnName);
178 },
179 [schemaName, tableName](AddIndex const& actualCommand) -> std::string {
180 using namespace std::string_view_literals;
181 auto const uniqueStr = actualCommand.unique ? "UNIQUE "sv : ""sv;
182 if (schemaName.empty())
183 return std::format(R"(CREATE {2}INDEX "{0}_{1}_index" ON "{0}" ("{1}");)",
184 tableName,
185 actualCommand.columnName,
186 uniqueStr);
187 else
188 return std::format(R"(CREATE {3}INDEX "{0}_{1}_{2}_index" ON "{0}"."{1}" ("{2}");)",
189 schemaName,
190 tableName,
191 actualCommand.columnName,
192 uniqueStr);
193 },
194 [schemaName, tableName](DropIndex const& actualCommand) -> std::string {
195 if (schemaName.empty())
196 return std::format(R"(DROP INDEX "{0}_{1}_index";)", tableName, actualCommand.columnName);
197 else
198 return std::format(
199 R"(DROP INDEX "{0}_{1}_{2}_index";)", schemaName, tableName, actualCommand.columnName);
200 },
201 [schemaName, tableName](AddForeignKey const& actualCommand) -> std::string {
202 return std::format(
203 R"(ALTER TABLE {} ADD {};)",
204 FormatTableName(schemaName, tableName),
205 BuildForeignKeyConstraint(tableName, actualCommand.columnName, actualCommand.referencedColumn));
206 },
207 [schemaName, tableName](DropForeignKey const& actualCommand) -> std::string {
208 return std::format(R"(ALTER TABLE {} DROP CONSTRAINT "{}";)",
209 FormatTableName(schemaName, tableName),
210 std::format("FK_{}_{}", tableName, actualCommand.columnName));
211 },
212 [schemaName, tableName](AddCompositeForeignKey const& actualCommand) -> std::string {
213 std::stringstream ss;
214 ss << "ALTER TABLE " << FormatTableName(schemaName, tableName) << " ADD CONSTRAINT "
215 << std::format("\"FK_{}_{}\"", tableName, actualCommand.columns[0]) << " FOREIGN KEY (";
216
217 size_t i = 0;
218 for (auto const& col: actualCommand.columns)
219 {
220 if (i++ > 0)
221 ss << ", ";
222 ss << '"' << col << '"';
223 }
224 ss << ") REFERENCES " << FormatTableName(schemaName, actualCommand.referencedTableName) << " (";
225
226 i = 0;
227 for (auto const& col: actualCommand.referencedColumns)
228 {
229 if (i++ > 0)
230 ss << ", ";
231 ss << '"' << col << '"';
232 }
233 ss << ");";
234 return ss.str();
235 },
236 [schemaName, tableName, this](AddColumnIfNotExists const& actualCommand) -> std::string {
237 // PostgreSQL has native IF NOT EXISTS support for ADD COLUMN
238 return std::format(R"(ALTER TABLE {} ADD COLUMN IF NOT EXISTS "{}" {} {};)",
239 FormatTableName(schemaName, tableName),
240 actualCommand.columnName,
241 ColumnType(actualCommand.columnType),
242 actualCommand.nullable == SqlNullable::NotNull ? "NOT NULL" : "NULL");
243 },
244 [schemaName, tableName](DropColumnIfExists const& actualCommand) -> std::string {
245 // PostgreSQL has native IF EXISTS support for DROP COLUMN
246 return std::format(R"(ALTER TABLE {} DROP COLUMN IF EXISTS "{}";)",
247 FormatTableName(schemaName, tableName),
248 actualCommand.columnName);
249 },
250 [schemaName, tableName](DropIndexIfExists const& actualCommand) -> std::string {
251 // PostgreSQL has native IF EXISTS support for DROP INDEX
252 if (schemaName.empty())
253 return std::format(
254 R"(DROP INDEX IF EXISTS "{0}_{1}_index";)", tableName, actualCommand.columnName);
255 else
256 return std::format(R"(DROP INDEX IF EXISTS "{0}_{1}_{2}_index";)",
257 schemaName,
258 tableName,
259 actualCommand.columnName);
260 },
261 },
262 command);
263 }
264
265 return { sqlQueryString.str() };
266 }
267
268 [[nodiscard]] std::string QueryServerVersion() const override
269 {
270 return "SELECT version()";
271 }
272};
273
274} // namespace Lightweight