server/storage/dbinitializer_p.cpp

0001 /***************************************************************************
0002  *   SPDX-FileCopyrightText: 2006 Tobias Koenig <tokoe@kde.org>            *
0003  *   SPDX-FileCopyrightText: 2010 Volker Krause <vkrause@kde.org>          *
0004  *                                                                         *
0005  *   SPDX-License-Identifier: LGPL-2.0-or-later                            *
0006  ***************************************************************************/
0007
0008 #include "storage/dbinitializer_p.h"
0009 #include "shared/akranges.h"
0010
0011 using namespace Akonadi;
0012 using namespace Akonadi::Server;
0013
0014 // BEGIN MySQL
0015
0016 DbInitializerMySql::DbInitializerMySql(const QSqlDatabase &database)
0017     : DbInitializer(database)
0018 {
0019 }
0020
0021 QString DbInitializerMySql::sqlType(const ColumnDescription &col, int size) const
0022 {
0023     if (col.type == QLatin1StringView("QString")) {
0024         return QLatin1StringView("VARBINARY(") + QString::number(size <= 0 ? 255 : size) + QLatin1StringView(")");
0025     } else {
0026         return DbInitializer::sqlType(col, size);
0027     }
0028 }
0029
0030 QString DbInitializerMySql::buildCreateTableStatement(const TableDescription &tableDescription) const
0031 {
0032     QStringList columns;
0033     QStringList references;
0034
0035     for (const ColumnDescription &columnDescription : tableDescription.columns) {
0036         columns.append(buildColumnStatement(columnDescription, tableDescription));
0037
0038         if (!columnDescription.refTable.isEmpty() && !columnDescription.refColumn.isEmpty()) {
0039             references << QStringLiteral("FOREIGN KEY (%1) REFERENCES %2Table(%3) ")
0040                               .arg(columnDescription.name, columnDescription.refTable, columnDescription.refColumn)
0041                     + buildReferentialAction(columnDescription.onUpdate, columnDescription.onDelete);
0042         }
0043     }
0044
0045     if (tableDescription.primaryKeyColumnCount() > 1) {
0046         columns.push_back(buildPrimaryKeyStatement(tableDescription));
0047     }
0048     columns << references;
0049
0050     QString tableProperties = QStringLiteral(" COLLATE=utf8_general_ci DEFAULT CHARSET=utf8");
0051     if (tableDescription.columns | AkRanges::Actions::any([](const auto &col) {
0052             return col.type == QLatin1StringView("QString") && col.size > 255;
0053         })) {
0054         tableProperties += QStringLiteral(" ROW_FORMAT=DYNAMIC");
0055     }
0056
0057     return QStringLiteral("CREATE TABLE %1 (%2) %3").arg(tableDescription.name, columns.join(QStringLiteral(", ")), tableProperties);
0058 }
0059
0060 QString DbInitializerMySql::buildColumnStatement(const ColumnDescription &columnDescription, const TableDescription &tableDescription) const
0061 {
0062     QString column = columnDescription.name;
0063
0064     column += QLatin1Char(' ') + sqlType(columnDescription, columnDescription.size);
0065
0066     if (!columnDescription.allowNull) {
0067         column += QLatin1StringView(" NOT NULL");
0068     }
0069
0070     if (columnDescription.isAutoIncrement) {
0071         column += QLatin1StringView(" AUTO_INCREMENT");
0072     }
0073
0074     if (columnDescription.isPrimaryKey && tableDescription.primaryKeyColumnCount() == 1) {
0075         column += QLatin1StringView(" PRIMARY KEY");
0076     }
0077
0078     if (columnDescription.isUnique) {
0079         column += QLatin1StringView(" UNIQUE");
0080     }
0081
0082     if (!columnDescription.defaultValue.isEmpty()) {
0083         const QString defaultValue = sqlValue(columnDescription, columnDescription.defaultValue);
0084
0085         if (!defaultValue.isEmpty()) {
0086             column += QStringLiteral(" DEFAULT %1").arg(defaultValue);
0087         }
0088     }
0089
0090     return column;
0091 }
0092
0093 QString DbInitializerMySql::buildInsertValuesStatement(const TableDescription &tableDescription, const DataDescription &dataDescription) const
0094 {
0095     QMap<QString, QString> data = dataDescription.data;
0096     QStringList keys;
0097     QStringList values;
0098     for (auto it = data.begin(), end = data.end(); it != end; ++it) {
0099         it.value().replace(QLatin1StringView("\\"), QLatin1StringView("\\\\"));
0100         keys.push_back(it.key());
0101         values.push_back(it.value());
0102     }
0103
0104     return QStringLiteral("INSERT INTO %1 (%2) VALUES (%3)").arg(tableDescription.name, keys.join(QLatin1Char(',')), values.join(QLatin1Char(',')));
0105 }
0106
0107 QStringList DbInitializerMySql::buildAddForeignKeyConstraintStatements(const TableDescription &table, const ColumnDescription &column) const
0108 {
0109     return {QStringLiteral("ALTER TABLE %1 ADD FOREIGN KEY (%2) REFERENCES %4Table(%5) %6")
0110                 .arg(table.name, column.name, column.refTable, column.refColumn, buildReferentialAction(column.onUpdate, column.onDelete))};
0111 }
0112
0113 QStringList DbInitializerMySql::buildRemoveForeignKeyConstraintStatements(const DbIntrospector::ForeignKey &fk, const TableDescription &table) const
0114 {
0115     return {QStringLiteral("ALTER TABLE %1 DROP FOREIGN KEY %2").arg(table.name, fk.name)};
0116 }
0117
0118 // END MySQL
0119
0120 // BEGIN Sqlite
0121
0122 DbInitializerSqlite::DbInitializerSqlite(const QSqlDatabase &database)
0123     : DbInitializer(database)
0124 {
0125 }
0126
0127 QString DbInitializerSqlite::buildCreateTableStatement(const TableDescription &tableDescription) const
0128 {
0129     QStringList columns;
0130
0131     columns.reserve(tableDescription.columns.count() + 1);
0132     for (const ColumnDescription &columnDescription : std::as_const(tableDescription.columns)) {
0133         columns.append(buildColumnStatement(columnDescription, tableDescription));
0134     }
0135
0136     if (tableDescription.primaryKeyColumnCount() > 1) {
0137         columns.push_back(buildPrimaryKeyStatement(tableDescription));
0138     }
0139     QStringList references;
0140     for (const ColumnDescription &columnDescription : std::as_const(tableDescription.columns)) {
0141         if (!columnDescription.refTable.isEmpty() && !columnDescription.refColumn.isEmpty()) {
0142             const auto constraintName =
0143                 QStringLiteral("%1%2_%3%4_fk").arg(tableDescription.name, columnDescription.name, columnDescription.refTable, columnDescription.refColumn);
0144             references << QStringLiteral("CONSTRAINT %1 FOREIGN KEY (%2) REFERENCES %3Table(%4) %5 DEFERRABLE INITIALLY DEFERRED")
0145                               .arg(constraintName,
0146                                    columnDescription.name,
0147                                    columnDescription.refTable,
0148                                    columnDescription.refColumn,
0149                                    buildReferentialAction(columnDescription.onUpdate, columnDescription.onDelete));
0150         }
0151     }
0152     columns << references;
0153
0154     return QStringLiteral("CREATE TABLE %1 (%2)").arg(tableDescription.name, columns.join(QStringLiteral(", ")));
0155 }
0156
0157 QString DbInitializerSqlite::buildColumnStatement(const ColumnDescription &columnDescription, const TableDescription &tableDescription) const
0158 {
0159     QString column = columnDescription.name + QLatin1Char(' ');
0160
0161     if (columnDescription.isAutoIncrement) {
0162         column += QLatin1StringView("INTEGER");
0163     } else {
0164         column += sqlType(columnDescription, columnDescription.size);
0165     }
0166
0167     if (columnDescription.isPrimaryKey && tableDescription.primaryKeyColumnCount() == 1) {
0168         column += QLatin1StringView(" PRIMARY KEY");
0169     } else if (columnDescription.isUnique) {
0170         column += QLatin1StringView(" UNIQUE");
0171     }
0172
0173     if (columnDescription.isAutoIncrement) {
0174         column += QLatin1StringView(" AUTOINCREMENT");
0175     }
0176
0177     if (!columnDescription.allowNull) {
0178         column += QLatin1StringView(" NOT NULL");
0179     }
0180
0181     if (!columnDescription.defaultValue.isEmpty()) {
0182         const QString defaultValue = sqlValue(columnDescription, columnDescription.defaultValue);
0183
0184         if (!defaultValue.isEmpty()) {
0185             column += QStringLiteral(" DEFAULT %1").arg(defaultValue);
0186         }
0187     }
0188
0189     return column;
0190 }
0191
0192 QString DbInitializerSqlite::buildInsertValuesStatement(const TableDescription &tableDescription, const DataDescription &dataDescription) const
0193 {
0194     QMap<QString, QString> data = dataDescription.data;
0195     QStringList keys;
0196     QStringList values;
0197     for (auto it = data.begin(), end = data.end(); it != end; ++it) {
0198         it.value().replace(QLatin1StringView("true"), QLatin1StringView("1"));
0199         it.value().replace(QLatin1StringView("false"), QLatin1StringView("0"));
0200         keys.push_back(it.key());
0201         values.push_back(it.value());
0202     }
0203
0204     return QStringLiteral("INSERT INTO %1 (%2) VALUES (%3)").arg(tableDescription.name, keys.join(QLatin1Char(',')), values.join(QLatin1Char(',')));
0205 }
0206
0207 QString DbInitializerSqlite::sqlValue(const ColumnDescription &col, const QString &value) const
0208 {
0209     if (col.type == QLatin1StringView("bool")) {
0210         if (value == QLatin1StringView("false")) {
0211             return QStringLiteral("0");
0212         } else if (value == QLatin1StringView("true")) {
0213             return QStringLiteral("1");
0214         }
0215         return value;
0216     }
0217
0218     return Akonadi::Server::DbInitializer::sqlValue(col, value);
0219 }
0220
0221 QStringList DbInitializerSqlite::buildAddForeignKeyConstraintStatements(const TableDescription &table, const ColumnDescription & /*column*/) const
0222 {
0223     return buildUpdateForeignKeyConstraintsStatements(table);
0224 }
0225
0226 QStringList DbInitializerSqlite::buildRemoveForeignKeyConstraintStatements(const DbIntrospector::ForeignKey & /*fk*/, const TableDescription &table) const
0227 {
0228     return buildUpdateForeignKeyConstraintsStatements(table);
0229 }
0230
0231 QStringList DbInitializerSqlite::buildUpdateForeignKeyConstraintsStatements(const TableDescription &table) const
0232 {
0233     // Unfortunately, SQLite does not support adding or removing foreign keys through ALTER TABLE,
0234     // this is the only way how to do it.
0235     return {QStringLiteral("PRAGMA defer_foreign_keys=ON"),
0236             QStringLiteral("BEGIN TRANSACTION"),
0237             QStringLiteral("ALTER TABLE %1 RENAME TO %1_old").arg(table.name),
0238             buildCreateTableStatement(table),
0239             QStringLiteral("INSERT INTO %1 SELECT * FROM %1_old").arg(table.name),
0240             QStringLiteral("DROP TABLE %1_old").arg(table.name),
0241             QStringLiteral("COMMIT"),
0242             QStringLiteral("PRAGMA defer_foreign_keys=OFF")};
0243 }
0244
0245 // END Sqlite
0246
0247 // BEGIN PostgreSQL
0248
0249 DbInitializerPostgreSql::DbInitializerPostgreSql(const QSqlDatabase &database)
0250     : DbInitializer(database)
0251 {
0252 }
0253
0254 QString DbInitializerPostgreSql::sqlType(const ColumnDescription &col, int size) const
0255 {
0256     if (col.type == QLatin1StringView("qint64")) {
0257         return QStringLiteral("int8");
0258     } else if (col.type == QLatin1StringView("QByteArray")) {
0259         return QStringLiteral("BYTEA");
0260     } else if (col.isEnum) {
0261         return QStringLiteral("SMALLINT");
0262     }
0263
0264     return DbInitializer::sqlType(col, size);
0265 }
0266
0267 QString DbInitializerPostgreSql::buildCreateTableStatement(const TableDescription &tableDescription) const
0268 {
0269     QStringList columns;
0270     columns.reserve(tableDescription.columns.size() + 1);
0271
0272     for (const ColumnDescription &columnDescription : tableDescription.columns) {
0273         columns.append(buildColumnStatement(columnDescription, tableDescription));
0274     }
0275
0276     if (tableDescription.primaryKeyColumnCount() > 1) {
0277         columns.push_back(buildPrimaryKeyStatement(tableDescription));
0278     }
0279
0280     return QStringLiteral("CREATE TABLE %1 (%2)").arg(tableDescription.name, columns.join(QStringLiteral(", ")));
0281 }
0282
0283 QString DbInitializerPostgreSql::buildColumnStatement(const ColumnDescription &columnDescription, const TableDescription &tableDescription) const
0284 {
0285     QString column = columnDescription.name + QLatin1Char(' ');
0286
0287     if (columnDescription.isAutoIncrement) {
0288         column += QLatin1StringView("SERIAL");
0289     } else {
0290         column += sqlType(columnDescription, columnDescription.size);
0291     }
0292
0293     if (columnDescription.isPrimaryKey && tableDescription.primaryKeyColumnCount() == 1) {
0294         column += QLatin1StringView(" PRIMARY KEY");
0295     } else if (columnDescription.isUnique) {
0296         column += QLatin1StringView(" UNIQUE");
0297     }
0298
0299     if (!columnDescription.allowNull && !(columnDescription.isPrimaryKey && tableDescription.primaryKeyColumnCount() == 1)) {
0300         column += QLatin1StringView(" NOT NULL");
0301     }
0302
0303     if (!columnDescription.defaultValue.isEmpty()) {
0304         const QString defaultValue = sqlValue(columnDescription, columnDescription.defaultValue);
0305
0306         if (!defaultValue.isEmpty()) {
0307             column += QStringLiteral(" DEFAULT %1").arg(defaultValue);
0308         }
0309     }
0310
0311     return column;
0312 }
0313
0314 QString DbInitializerPostgreSql::buildInsertValuesStatement(const TableDescription &tableDescription, const DataDescription &dataDescription) const
0315 {
0316     QStringList keys;
0317     QStringList values;
0318     for (auto it = dataDescription.data.cbegin(), end = dataDescription.data.cend(); it != end; ++it) {
0319         keys.push_back(it.key());
0320         values.push_back(it.value());
0321     }
0322
0323     return QStringLiteral("INSERT INTO %1 (%2) VALUES (%3)").arg(tableDescription.name, keys.join(QLatin1Char(',')), values.join(QLatin1Char(',')));
0324 }
0325
0326 QStringList DbInitializerPostgreSql::buildAddForeignKeyConstraintStatements(const TableDescription &table, const ColumnDescription &column) const
0327 {
0328     // constraints must have name in PostgreSQL
0329     const QString constraintName = table.name + column.name + QLatin1StringView("_") + column.refTable + column.refColumn + QLatin1StringView("_fk");
0330     return {QStringLiteral("ALTER TABLE %1 ADD CONSTRAINT %2 FOREIGN KEY (%3) REFERENCES %4Table(%5) %6 DEFERRABLE INITIALLY DEFERRED")
0331                 .arg(table.name, constraintName, column.name, column.refTable, column.refColumn, buildReferentialAction(column.onUpdate, column.onDelete))};
0332 }
0333
0334 QStringList DbInitializerPostgreSql::buildRemoveForeignKeyConstraintStatements(const DbIntrospector::ForeignKey &fk, const TableDescription &table) const
0335 {
0336     return {QStringLiteral("ALTER TABLE %1 DROP CONSTRAINT %2").arg(table.name, fk.name)};
0337 }
0338
0339 // END PostgreSQL