diff --git a/autotests/libs/itemappendtest.cpp b/autotests/libs/itemappendtest.cpp index 5a4797bc9..4cc683a1e 100644 --- a/autotests/libs/itemappendtest.cpp +++ b/autotests/libs/itemappendtest.cpp @@ -1,401 +1,403 @@ /* Copyright (c) 2006 Volker Krause This library is free software; you can redistribute it and/or modify it under the terms of the GNU Library General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Library General Public License for more details. You should have received a copy of the GNU Library General Public License along with this library; see the file COPYING.LIB. If not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. */ #include "itemappendtest.h" #include "control.h" #include "testattribute.h" #include "test_utils.h" #include #include #include #include #include #include #include #include #include using namespace Akonadi; QTEST_AKONADIMAIN(ItemAppendTest) void ItemAppendTest::initTestCase() { AkonadiTest::checkTestIsIsolated(); Control::start(); AkonadiTest::setAllResourcesOffline(); AttributeFactory::registerAttribute(); } void ItemAppendTest::testItemAppend_data() { QTest::addColumn("remoteId"); QTest::newRow("empty") << QString(); QTest::newRow("non empty") << QStringLiteral("remote-id"); QTest::newRow("whitespace") << QStringLiteral("remote id"); QTest::newRow("quotes") << QStringLiteral("\"remote\" id"); QTest::newRow("brackets") << QStringLiteral("[remote id]"); + QTest::newRow("RID length limit") << QStringLiteral("a").repeated(1024); } void ItemAppendTest::testItemAppend() { const Collection testFolder1(collectionIdFromPath(QStringLiteral("res2/space folder"))); QVERIFY(testFolder1.isValid()); QFETCH(QString, remoteId); Item ref; // for cleanup Item item(-1); item.setRemoteId(remoteId); item.setMimeType(QStringLiteral("application/octet-stream")); item.setFlag("TestFlag"); item.setSize(3456); ItemCreateJob *job = new ItemCreateJob(item, testFolder1, this); AKVERIFYEXEC(job); ref = job->item(); QCOMPARE(ref.parentCollection(), testFolder1); ItemFetchJob *fjob = new ItemFetchJob(testFolder1, this); fjob->fetchScope().setAncestorRetrieval(ItemFetchScope::Parent); AKVERIFYEXEC(fjob); QCOMPARE(fjob->items().count(), 1); QCOMPARE(fjob->items()[0], ref); QCOMPARE(fjob->items()[0].remoteId(), remoteId); QVERIFY(fjob->items()[0].flags().contains("TestFlag")); QCOMPARE(fjob->items()[0].parentCollection(), ref.parentCollection()); qint64 size = 3456; QCOMPARE(fjob->items()[0].size(), size); ItemDeleteJob *djob = new ItemDeleteJob(ref, this); AKVERIFYEXEC(djob); fjob = new ItemFetchJob(testFolder1, this); AKVERIFYEXEC(fjob); QVERIFY(fjob->items().isEmpty()); } void ItemAppendTest::testContent_data() { QTest::addColumn("data"); QTest::newRow("null") << QByteArray(); QTest::newRow("empty") << QByteArray(""); QTest::newRow("nullbyte") << QByteArray("\0", 1); QTest::newRow("nullbyte2") << QByteArray("\0X", 2); QString utf8string = QStringLiteral("äöüß@€µøđ¢©®"); QTest::newRow("utf8") << utf8string.toUtf8(); QTest::newRow("newlines") << QByteArray("\nsome\n\nbreaked\ncontent\n\n"); QByteArray b; QTest::newRow("big") << b.fill('a', 1 << 20); QTest::newRow("bignull") << b.fill('\0', 1 << 20); QTest::newRow("bigcr") << b.fill('\r', 1 << 20); QTest::newRow("biglf") << b.fill('\n', 1 << 20); } void ItemAppendTest::testContent() { const Collection testFolder1(collectionIdFromPath(QStringLiteral("res2/space folder"))); QVERIFY(testFolder1.isValid()); QFETCH(QByteArray, data); Item item; item.setMimeType(QStringLiteral("application/octet-stream")); if (!data.isNull()) { item.setPayload(data); } ItemCreateJob *job = new ItemCreateJob(item, testFolder1, this); AKVERIFYEXEC(job); Item ref = job->item(); ItemFetchJob *fjob = new ItemFetchJob(testFolder1, this); fjob->fetchScope().setCacheOnly(true); fjob->fetchScope().fetchFullPayload(); AKVERIFYEXEC(fjob); QCOMPARE(fjob->items().count(), 1); Item item2 = fjob->items().first(); QCOMPARE(item2.hasPayload(), !data.isNull()); if (item2.hasPayload()) { QCOMPARE(item2.payload(), data); } ItemDeleteJob *djob = new ItemDeleteJob(ref, this); AKVERIFYEXEC(djob); } void ItemAppendTest::testNewMimetype() { const Collection col(collectionIdFromPath(QStringLiteral("res2/space folder"))); QVERIFY(col.isValid()); Item item; item.setMimeType(QStringLiteral("application/new-type")); ItemCreateJob *job = new ItemCreateJob(item, col, this); AKVERIFYEXEC(job); item = job->item(); QVERIFY(item.isValid()); ItemFetchJob *fetch = new ItemFetchJob(item, this); AKVERIFYEXEC(fetch); QCOMPARE(fetch->items().count(), 1); QCOMPARE(fetch->items().first().mimeType(), item.mimeType()); } void ItemAppendTest::testIllegalAppend() { const Collection testFolder1(collectionIdFromPath(QStringLiteral("res2/space folder"))); QVERIFY(testFolder1.isValid()); Item item; item.setMimeType(QStringLiteral("application/octet-stream")); // adding item to non-existing collection ItemCreateJob *job = new ItemCreateJob(item, Collection(INT_MAX), this); QVERIFY(!job->exec()); // adding item into a collection which can't handle items of this type const Collection col(collectionIdFromPath(QStringLiteral("res1/foo/bla"))); QVERIFY(col.isValid()); job = new ItemCreateJob(item, col, this); QEXPECT_FAIL("", "Test not yet implemented in the server.", Continue); QVERIFY(!job->exec()); } void ItemAppendTest::testMultipartAppend() { const Collection testFolder1(collectionIdFromPath(QStringLiteral("res2/space folder"))); QVERIFY(testFolder1.isValid()); Item item; item.setMimeType(QStringLiteral("application/octet-stream")); item.setPayload("body data"); item.attribute(Item::AddIfMissing)->data = "extra data"; item.setFlag("TestFlag"); ItemCreateJob *job = new ItemCreateJob(item, testFolder1, this); AKVERIFYEXEC(job); Item ref = job->item(); ItemFetchJob *fjob = new ItemFetchJob(ref, this); fjob->fetchScope().fetchFullPayload(); fjob->fetchScope().fetchAttribute(); AKVERIFYEXEC(fjob); QCOMPARE(fjob->items().count(), 1); item = fjob->items().first(); QCOMPARE(item.payload(), QByteArray("body data")); QVERIFY(item.hasAttribute()); QCOMPARE(item.attribute()->data, QByteArray("extra data")); QVERIFY(item.flags().contains("TestFlag")); ItemDeleteJob *djob = new ItemDeleteJob(ref, this); AKVERIFYEXEC(djob); } void ItemAppendTest::testInvalidMultipartAppend() { Item item; item.setMimeType(QStringLiteral("application/octet-stream")); item.setPayload("body data"); item.attribute(Item::AddIfMissing)->data = "extra data"; item.setFlag("TestFlag"); ItemCreateJob *job = new ItemCreateJob(item, Collection(-1), this); QVERIFY(!job->exec()); Item item2; item2.setMimeType(QStringLiteral("application/octet-stream")); item2.setPayload("more body data"); item2.attribute(Item::AddIfMissing)->data = "even more extra data"; item2.setFlag("TestFlag"); ItemCreateJob *job2 = new ItemCreateJob(item2, Collection(-1), this); QVERIFY(!job2->exec()); } void ItemAppendTest::testItemSize_data() { QTest::addColumn("item"); QTest::addColumn("size"); Item i(QStringLiteral("application/octet-stream")); i.setPayload(QByteArray("ABCD")); QTest::newRow("auto size") << i << 4ll; i.setSize(3); QTest::newRow("too small") << i << 4ll; i.setSize(10); QTest::newRow("too large") << i << 10ll; } void ItemAppendTest::testItemSize() { QFETCH(Akonadi::Item, item); QFETCH(qint64, size); const Collection col(collectionIdFromPath(QStringLiteral("res2/space folder"))); QVERIFY(col.isValid()); ItemCreateJob *create = new ItemCreateJob(item, col, this); AKVERIFYEXEC(create); Item newItem = create->item(); ItemFetchJob *fetch = new ItemFetchJob(newItem, this); AKVERIFYEXEC(fetch); QCOMPARE(fetch->items().count(), 1); QCOMPARE(fetch->items().first().size(), size); } void ItemAppendTest::testItemMerge_data() { QTest::addColumn("item1"); QTest::addColumn("item2"); QTest::addColumn("mergedItem"); QTest::addColumn("silent"); { Item i1(QStringLiteral("application/octet-stream")); i1.setPayload(QByteArray("ABCD")); i1.setSize(4); i1.setRemoteId(QStringLiteral("XYZ")); i1.setGid(QStringLiteral("XYZ")); i1.setFlag("TestFlag1"); i1.setRemoteRevision(QStringLiteral("5")); Item i2(QStringLiteral("application/octet-stream")); i2.setPayload(QByteArray("DEFGH")); i2.setSize(5); i2.setRemoteId(QStringLiteral("XYZ")); i2.setGid(QStringLiteral("XYZ")); i2.setFlag("TestFlag2"); i2.setRemoteRevision(QStringLiteral("6")); Item mergedItem(i2); mergedItem.setFlag("TestFlag1"); QTest::newRow("merge") << i1 << i2 << mergedItem << false; QTest::newRow("merge (silent)") << i1 << i2 << mergedItem << true; } { Item i1(QStringLiteral("application/octet-stream")); i1.setPayload(QByteArray("ABCD")); i1.setSize(4); i1.setRemoteId(QStringLiteral("RID2")); i1.setGid(QStringLiteral("GID2")); i1.setFlag("TestFlag1"); i1.setRemoteRevision(QStringLiteral("5")); Item i2(QStringLiteral("application/octet-stream")); i2.setRemoteId(QStringLiteral("RID2")); i2.setGid(QStringLiteral("GID2")); i2.setFlags(Item::Flags() << "TestFlag2"); i2.setRemoteRevision(QStringLiteral("6")); Item mergedItem(i1); mergedItem.setFlags(i2.flags()); mergedItem.setRemoteRevision(i2.remoteRevision()); QTest::newRow("overwrite flags, and don't remove existing payload") << i1 << i2 << mergedItem << false; QTest::newRow("overwrite flags, and don't remove existing payload (silent)") << i1 << i2 << mergedItem << true; } } void ItemAppendTest::testItemMerge() { QFETCH(Akonadi::Item, item1); QFETCH(Akonadi::Item, item2); QFETCH(Akonadi::Item, mergedItem); QFETCH(bool, silent); const Collection col(collectionIdFromPath(QStringLiteral("res2/space folder"))); QVERIFY(col.isValid()); ItemCreateJob *create = new ItemCreateJob(item1, col, this); AKVERIFYEXEC(create); const Item createdItem = create->item(); ItemCreateJob *merge = new ItemCreateJob(item2, col, this); ItemCreateJob::MergeOptions options = ItemCreateJob::GID | ItemCreateJob::RID; if (silent) { options |= ItemCreateJob::Silent; } merge->setMerge(options); AKVERIFYEXEC(merge); QCOMPARE(merge->item().id(), createdItem.id()); if (!silent) { QCOMPARE(merge->item().gid(), mergedItem.gid()); QCOMPARE(merge->item().remoteId(), mergedItem.remoteId()); QCOMPARE(merge->item().remoteRevision(), mergedItem.remoteRevision()); QCOMPARE(merge->item().payloadData(), mergedItem.payloadData()); QCOMPARE(merge->item().size(), mergedItem.size()); qDebug() << merge->item().flags() << mergedItem.flags(); QCOMPARE(merge->item().flags(), mergedItem.flags()); } if (merge->item().id() != createdItem.id()) { ItemDeleteJob *del = new ItemDeleteJob(merge->item(), this); AKVERIFYEXEC(del); } ItemDeleteJob *del = new ItemDeleteJob(createdItem, this); AKVERIFYEXEC(del); } void ItemAppendTest::testForeignPayload() { const Collection col(collectionIdFromPath(QStringLiteral("res2/space folder"))); QVERIFY(col.isValid()); const QString filePath = QString::fromUtf8(qgetenv("TMPDIR")) + QStringLiteral("/foreignPayloadFile.mbox"); QFile file(filePath); QVERIFY(file.open(QIODevice::WriteOnly)); file.write("123456789"); file.close(); Item item(QStringLiteral("application/octet-stream")); item.setPayloadPath(filePath); item.setRemoteId(QStringLiteral("RID3")); item.setSize(9); ItemCreateJob *create = new ItemCreateJob(item, col, this); AKVERIFYEXEC(create); auto ref = create->item(); ItemFetchJob *fetch = new ItemFetchJob(ref, this); fetch->fetchScope().fetchFullPayload(true); AKVERIFYEXEC(fetch); const auto items = fetch->items(); QCOMPARE(items.size(), 1); item = items[0]; QVERIFY(item.hasPayload()); QCOMPARE(item.payload(), QByteArray("123456789")); ItemDeleteJob *del = new ItemDeleteJob(item, this); AKVERIFYEXEC(del); // Make sure Akonadi does not delete a foreign payload QVERIFY(file.exists()); QVERIFY(file.remove()); } + diff --git a/src/server/storage/akonadidb.xml b/src/server/storage/akonadidb.xml index 0239b80c6..6b688f496 100644 --- a/src/server/storage/akonadidb.xml +++ b/src/server/storage/akonadidb.xml @@ -1,258 +1,258 @@ Contains the schema version of the database. - +
This meta data is stored inside akonadi to provide fast access.
- + create/modified time read access time Indicates that this item has unsaved changes.
This meta data is stored inside akonadi to provide fast access.
Table containing item part types. Part name, without namespace. Part namespace.
Specifies allowed MimeType for a Collection Used to associate items with search folders. diff --git a/src/server/storage/dbinitializer_p.cpp b/src/server/storage/dbinitializer_p.cpp index 18cdd4a51..43006e451 100644 --- a/src/server/storage/dbinitializer_p.cpp +++ b/src/server/storage/dbinitializer_p.cpp @@ -1,370 +1,375 @@ /*************************************************************************** * Copyright (C) 2006 by Tobias Koenig * * Copyright (C) 2010 by Volker Krause * * * * This program is free software; you can redistribute it and/or modify * * it under the terms of the GNU Library General Public License as * * published by the Free Software Foundation; either version 2 of the * * License, or (at your option) any later version. * * * * This program is distributed in the hope that it will be useful, * * but WITHOUT ANY WARRANTY; without even the implied warranty of * * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * * GNU General Public License for more details. * * * * You should have received a copy of the GNU Library General Public * * License along with this program; if not, write to the * * Free Software Foundation, Inc., * * 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. * ***************************************************************************/ #include "storage/dbinitializer_p.h" +#include +using namespace Akonadi; using namespace Akonadi::Server; //BEGIN MySQL DbInitializerMySql::DbInitializerMySql(const QSqlDatabase &database) : DbInitializer(database) { } bool DbInitializerMySql::hasForeignKeyConstraints() const { return true; } QString DbInitializerMySql::sqlType(const ColumnDescription &col, int size) const { if (col.type == QLatin1String("QString")) { return QLatin1Literal("VARBINARY(") + QString::number(size <= 0 ? 255 : size) + QLatin1Literal(")"); } else { return DbInitializer::sqlType(col, size); } } QString DbInitializerMySql::buildCreateTableStatement(const TableDescription &tableDescription) const { QStringList columns; QStringList references; Q_FOREACH (const ColumnDescription &columnDescription, tableDescription.columns) { columns.append(buildColumnStatement(columnDescription, tableDescription)); if (!columnDescription.refTable.isEmpty() && !columnDescription.refColumn.isEmpty()) { references << QStringLiteral("FOREIGN KEY (%1) REFERENCES %2Table(%3) ") .arg(columnDescription.name, columnDescription.refTable, columnDescription.refColumn) + buildReferentialAction(columnDescription.onUpdate, columnDescription.onDelete); } } if (tableDescription.primaryKeyColumnCount() > 1) { columns.push_back(buildPrimaryKeyStatement(tableDescription)); } columns << references; - const QString tableProperties = QStringLiteral(" COLLATE=utf8_general_ci DEFAULT CHARSET=utf8"); + QString tableProperties = QStringLiteral(" COLLATE=utf8_general_ci DEFAULT CHARSET=utf8"); + if (tableDescription.columns | any([](const auto &col) { return col.type == QLatin1String("QString") && col.size > 255; })) { + tableProperties += QStringLiteral(" ROW_FORMAT=DYNAMIC"); + } return QStringLiteral("CREATE TABLE %1 (%2) %3").arg(tableDescription.name, columns.join(QStringLiteral(", ")), tableProperties); } QString DbInitializerMySql::buildColumnStatement(const ColumnDescription &columnDescription, const TableDescription &tableDescription) const { QString column = columnDescription.name; column += QLatin1Char(' ') + sqlType(columnDescription, columnDescription.size); if (!columnDescription.allowNull) { column += QLatin1String(" NOT NULL"); } if (columnDescription.isAutoIncrement) { column += QLatin1String(" AUTO_INCREMENT"); } if (columnDescription.isPrimaryKey && tableDescription.primaryKeyColumnCount() == 1) { column += QLatin1String(" PRIMARY KEY"); } if (columnDescription.isUnique) { column += QLatin1String(" UNIQUE"); } if (!columnDescription.defaultValue.isEmpty()) { const QString defaultValue = sqlValue(columnDescription, columnDescription.defaultValue); if (!defaultValue.isEmpty()) { column += QStringLiteral(" DEFAULT %1").arg(defaultValue); } } return column; } QString DbInitializerMySql::buildInsertValuesStatement(const TableDescription &tableDescription, const DataDescription &dataDescription) const { QMap data = dataDescription.data; QMutableMapIterator it(data); while (it.hasNext()) { it.next(); it.value().replace(QLatin1String("\\"), QLatin1String("\\\\")); } return QStringLiteral("INSERT INTO %1 (%2) VALUES (%3)") .arg(tableDescription.name, QStringList(data.keys()).join(QLatin1Char(',')), QStringList(data.values()).join(QLatin1Char(','))); } QStringList DbInitializerMySql::buildAddForeignKeyConstraintStatements(const TableDescription &table, const ColumnDescription &column) const { return { QStringLiteral("ALTER TABLE %1 ADD FOREIGN KEY (%2) REFERENCES %4Table(%5) %6") .arg(table.name, column.name, column.refTable, column.refColumn, buildReferentialAction(column.onUpdate, column.onDelete)) }; } QStringList DbInitializerMySql::buildRemoveForeignKeyConstraintStatements(const DbIntrospector::ForeignKey &fk, const TableDescription &table) const { return { QStringLiteral("ALTER TABLE %1 DROP FOREIGN KEY %2").arg(table.name, fk.name) }; } //END MySQL //BEGIN Sqlite DbInitializerSqlite::DbInitializerSqlite(const QSqlDatabase &database) : DbInitializer(database) { } bool DbInitializerSqlite::hasForeignKeyConstraints() const { return true; } QString DbInitializerSqlite::buildCreateTableStatement(const TableDescription &tableDescription) const { QStringList columns; columns.reserve(tableDescription.columns.count() + 1); for (const ColumnDescription &columnDescription : qAsConst(tableDescription.columns)) { columns.append(buildColumnStatement(columnDescription, tableDescription)); } if (tableDescription.primaryKeyColumnCount() > 1) { columns.push_back(buildPrimaryKeyStatement(tableDescription)); } QStringList references; for (const ColumnDescription &columnDescription : qAsConst(tableDescription.columns)) { if (!columnDescription.refTable.isEmpty() && !columnDescription.refColumn.isEmpty()) { const auto constraintName = QStringLiteral("%1%2_%3%4_fk").arg(tableDescription.name, columnDescription.name, columnDescription.refTable, columnDescription.refColumn); references << QStringLiteral("CONSTRAINT %1 FOREIGN KEY (%2) REFERENCES %3Table(%4) %5 DEFERRABLE INITIALLY DEFERRED") .arg(constraintName, columnDescription.name, columnDescription.refTable, columnDescription.refColumn, buildReferentialAction(columnDescription.onUpdate, columnDescription.onDelete)); } } columns << references; return QStringLiteral("CREATE TABLE %1 (%2)").arg(tableDescription.name, columns.join(QStringLiteral(", "))); } QString DbInitializerSqlite::buildColumnStatement(const ColumnDescription &columnDescription, const TableDescription &tableDescription) const { QString column = columnDescription.name + QLatin1Char(' '); if (columnDescription.isAutoIncrement) { column += QLatin1String("INTEGER"); } else { column += sqlType(columnDescription, columnDescription.size); } if (columnDescription.isPrimaryKey && tableDescription.primaryKeyColumnCount() == 1) { column += QLatin1String(" PRIMARY KEY"); } else if (columnDescription.isUnique) { column += QLatin1String(" UNIQUE"); } if (columnDescription.isAutoIncrement) { column += QLatin1String(" AUTOINCREMENT"); } if (!columnDescription.allowNull) { column += QLatin1String(" NOT NULL"); } if (!columnDescription.defaultValue.isEmpty()) { const QString defaultValue = sqlValue(columnDescription, columnDescription.defaultValue); if (!defaultValue.isEmpty()) { column += QStringLiteral(" DEFAULT %1").arg(defaultValue); } } return column; } QString DbInitializerSqlite::buildInsertValuesStatement(const TableDescription &tableDescription, const DataDescription &dataDescription) const { QMap data = dataDescription.data; QMutableMapIterator it(data); while (it.hasNext()) { it.next(); it.value().replace(QLatin1String("true"), QLatin1String("1")); it.value().replace(QLatin1String("false"), QLatin1String("0")); } return QStringLiteral("INSERT INTO %1 (%2) VALUES (%3)") .arg(tableDescription.name, QStringList(data.keys()).join(QLatin1Char(',')), QStringList(data.values()).join(QLatin1Char(','))); } QString DbInitializerSqlite::sqlValue(const ColumnDescription &col, const QString &value) const { if (col.type == QLatin1String("bool")) { if (value == QLatin1String("false")) { return QStringLiteral("0"); } else if (value == QLatin1String("true")) { return QStringLiteral("1"); } return value; } return Akonadi::Server::DbInitializer::sqlValue(col, value); } QStringList DbInitializerSqlite::buildAddForeignKeyConstraintStatements(const TableDescription &table, const ColumnDescription &) const { return buildUpdateForeignKeyConstraintsStatements(table); } QStringList DbInitializerSqlite::buildRemoveForeignKeyConstraintStatements(const DbIntrospector::ForeignKey &, const TableDescription &table) const { return buildUpdateForeignKeyConstraintsStatements(table); } QStringList DbInitializerSqlite::buildUpdateForeignKeyConstraintsStatements(const TableDescription &table) const { // Unforunately, SQLite does not support add or removing foreign keys through ALTER TABLE, // this is the only way how to do it. return { QStringLiteral("PRAGMA defer_foreign_keys=ON"), QStringLiteral("BEGIN TRANSACTION"), QStringLiteral("ALTER TABLE %1 RENAME TO %1_old").arg(table.name), buildCreateTableStatement(table), QStringLiteral("INSERT INTO %1 SELECT * FROM %1_old").arg(table.name), QStringLiteral("DROP TABLE %1_old").arg(table.name), QStringLiteral("COMMIT"), QStringLiteral("PRAGMA defer_foreign_keys=OFF") }; } //END Sqlite //BEGIN PostgreSQL DbInitializerPostgreSql::DbInitializerPostgreSql(const QSqlDatabase &database) : DbInitializer(database) { } bool DbInitializerPostgreSql::hasForeignKeyConstraints() const { return true; } QString DbInitializerPostgreSql::sqlType(const ColumnDescription &col, int size) const { if (col.type == QLatin1String("qint64")) { return QStringLiteral("int8"); } else if (col.type == QLatin1String("QByteArray")) { return QStringLiteral("BYTEA"); } else if (col.isEnum) { return QStringLiteral("SMALLINT"); } return DbInitializer::sqlType(col, size); } QString DbInitializerPostgreSql::buildCreateTableStatement(const TableDescription &tableDescription) const { QStringList columns; columns.reserve(tableDescription.columns.size() + 1); Q_FOREACH (const ColumnDescription &columnDescription, tableDescription.columns) { columns.append(buildColumnStatement(columnDescription, tableDescription)); } if (tableDescription.primaryKeyColumnCount() > 1) { columns.push_back(buildPrimaryKeyStatement(tableDescription)); } return QStringLiteral("CREATE TABLE %1 (%2)").arg(tableDescription.name, columns.join(QStringLiteral(", "))); } QString DbInitializerPostgreSql::buildColumnStatement(const ColumnDescription &columnDescription, const TableDescription &tableDescription) const { QString column = columnDescription.name + QLatin1Char(' '); if (columnDescription.isAutoIncrement) { column += QLatin1String("SERIAL"); } else { column += sqlType(columnDescription, columnDescription.size); } if (columnDescription.isPrimaryKey && tableDescription.primaryKeyColumnCount() == 1) { column += QLatin1String(" PRIMARY KEY"); } else if (columnDescription.isUnique) { column += QLatin1String(" UNIQUE"); } if (!columnDescription.allowNull && !(columnDescription.isPrimaryKey && tableDescription.primaryKeyColumnCount() == 1)) { column += QLatin1String(" NOT NULL"); } if (!columnDescription.defaultValue.isEmpty()) { const QString defaultValue = sqlValue(columnDescription, columnDescription.defaultValue); if (!defaultValue.isEmpty()) { column += QStringLiteral(" DEFAULT %1").arg(defaultValue); } } return column; } QString DbInitializerPostgreSql::buildInsertValuesStatement(const TableDescription &tableDescription, const DataDescription &dataDescription) const { QMap data = dataDescription.data; return QStringLiteral("INSERT INTO %1 (%2) VALUES (%3)") .arg(tableDescription.name, QStringList(data.keys()).join(QLatin1Char(',')), QStringList(data.values()).join(QLatin1Char(','))); } QStringList DbInitializerPostgreSql::buildAddForeignKeyConstraintStatements(const TableDescription &table, const ColumnDescription &column) const { // constraints must have name in PostgreSQL const QString constraintName = table.name + column.name + QLatin1Literal("_") + column.refTable + column.refColumn + QLatin1Literal("_fk"); return { QStringLiteral("ALTER TABLE %1 ADD CONSTRAINT %2 FOREIGN KEY (%3) REFERENCES %4Table(%5) %6 DEFERRABLE INITIALLY DEFERRED") .arg(table.name, constraintName, column.name, column.refTable, column.refColumn, buildReferentialAction(column.onUpdate, column.onDelete)) }; } QStringList DbInitializerPostgreSql::buildRemoveForeignKeyConstraintStatements(const DbIntrospector::ForeignKey &fk, const TableDescription &table) const { return { QStringLiteral("ALTER TABLE %1 DROP CONSTRAINT %2").arg(table.name, fk.name) }; } //END PostgreSQL diff --git a/src/server/storage/dbupdate.xml b/src/server/storage/dbupdate.xml index 0dbf56b3c..e702b0d93 100644 --- a/src/server/storage/dbupdate.xml +++ b/src/server/storage/dbupdate.xml @@ -1,349 +1,355 @@ ALTER TABLE LocationTable DROP COLUMN existCount; ALTER TABLE LocationTable DROP COLUMN recentCount; ALTER TABLE LocationTable DROP COLUMN unseenCount; ALTER TABLE LocationTable DROP COLUMN firstUnseen; UPDATE LocationTable SET subscribed = true; ALTER TABLE LocationTable DROP COLUMN cachePolicyId; ALTER TABLE ResourceTable DROP COLUMN cachePolicyId; DROP TABLE CachePolicyTable; UPDATE PartTable SET name = 'PLD:ENVELOPE' WHERE name = 'ENVELOPE'; UPDATE PartTable SET name = 'PLD:RFC822' WHERE name = 'RFC822'; UPDATE PartTable SET name = 'PLD:HEAD' WHERE name = 'HEAD'; UPDATE PartTable SET name = concat( 'ATR:', name ) WHERE substr( name, 1, 4 ) != 'PLD:'; DROP TABLE CollectionTable; ALTER TABLE LocationTable RENAME TO CollectionTable; ALTER TABLE PimItemTable DROP COLUMN collectionId; ALTER TABLE PimItemTable CHANGE locationId collectionId BIGINT; DROP TABLE CollectionAttributeTable; ALTER TABLE LocationAttributeTable CHANGE locationId collectionId BIGINT; ALTER TABLE LocationAttributeTable RENAME TO CollectionAttributeTable; DROP TABLE CollectionMimeTypeRelation; ALTER TABLE LocationMimeTypeRelation CHANGE Location_Id Collection_Id BIGINT NOT NULL DEFAULT '0'; ALTER TABLE LocationMimeTypeRelation RENAME TO CollectionMimeTypeRelation; DROP TABLE CollectionPimItemRelation; ALTER TABLE LocationPimItemRelation CHANGE Location_Id Collection_Id BIGINT NOT NULL DEFAULT '0'; ALTER TABLE LocationPimItemRelation RENAME TO CollectionPimItemRelation; ALTER TABLE PartTable CHANGE datasize datasize BIGINT; UPDATE CollectionTable SET parentId = NULL WHERE parentId = 0; ALTER TABLE CollectionTable CHANGE parentId parentId BIGINT DEFAULT NULL; UPDATE ResourceTable SET isVirtual = true WHERE name = 'akonadi_nepomuktag_resource'; UPDATE ResourceTable SET isVirtual = true WHERE name = 'akonadi_search_resource'; UPDATE CollectionTable SET queryString = remoteId WHERE resourceId = 1 AND parentId IS NOT NULL; UPDATE CollectionTable SET queryLanguage = 'SPARQL' WHERE resourceId = 1 AND parentId IS NOT NULL; ALTER TABLE CollectionAttributeTable CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE CollectionMimeTypeRelation CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE CollectionPimItemRelation CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE CollectionTable CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE FlagTable CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE MimeTypeTable CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE PartTable CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE PimItemFlagRelation CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE PimitemTable CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE ResourceTable CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE SchemaVersionTable CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE ResourceTable CHANGE name name VARCHAR(255) BINARY UNIQUE; ALTER TABLE CollectionTable CHANGE remoteId remoteId VARCHAR(255) BINARY; ALTER TABLE CollectionTable CHANGE remoteRevision remoteRevision VARCHAR(255) BINARY; ALTER TABLE CollectionTable CHANGE name name VARCHAR(255) BINARY; ALTER TABLE CollectionTable CHANGE cachePolicyLocalParts cachePolicyLocalParts VARCHAR(255) BINARY; ALTER TABLE CollectionTable CHANGE queryString queryString VARCHAR(255) BINARY; ALTER TABLE CollectionTable CHANGE queryLanguage queryLanguage VARCHAR(255) BINARY; ALTER TABLE MimeTypeTable CHANGE name name VARCHAR(255) BINARY UNIQUE; ALTER TABLE PimItemTable CHANGE remoteId remoteId VARCHAR(255) BINARY; ALTER TABLE PimItemTable CHANGE remoteRevision remoteRevision VARCHAR(255) BINARY; ALTER TABLE FlagTable CHANGE name name VARCHAR(255) BINARY UNIQUE; ALTER TABLE PartTable CHANGE name name VARCHAR(255) BINARY; ALTER TABLE ResourceTable CHANGE name name VARBINARY(255) UNIQUE; ALTER TABLE CollectionTable CHANGE remoteId remoteId VARBINARY(255); ALTER TABLE CollectionTable CHANGE remoteRevision remoteRevision VARBINARY(255); ALTER TABLE CollectionTable CHANGE name name VARBINARY(255); ALTER TABLE CollectionTable CHANGE cachePolicyLocalParts cachePolicyLocalParts VARBINARY(255); ALTER TABLE CollectionTable CHANGE queryString queryString VARBINARY(255); ALTER TABLE CollectionTable CHANGE queryLanguage queryLanguage VARBINARY(255); ALTER TABLE MimeTypeTable CHANGE name name VARBINARY(255) UNIQUE; ALTER TABLE PimItemTable CHANGE remoteId remoteId VARBINARY(255); ALTER TABLE PimItemTable CHANGE remoteRevision remoteRevision VARBINARY(255); ALTER TABLE FlagTable CHANGE name name VARBINARY(255) UNIQUE; ALTER TABLE PartTable CHANGE name name VARBINARY(255); UPDATE PimItemFlagRelation SET Flag_id=(SELECT id FROM FlagTable WHERE name='\\SEEN') WHERE Flag_id=(SELECT id FROM FlagTable WHERE name='\\Seen'); DELETE FROM FlagTable WHERE name='\\Seen'; ALTER TABLE CollectionTable CHANGE queryString queryString VARBINARY(1024); ALTER TABLE CollectionTable CHANGE queryString queryString VARBINARY(32768); ALTER TABLE PimItemFlagRelation CHANGE PimItem_id PimItem_id BIGINT NOT NULL ALTER TABLE PimItemFlagRelation CHANGE Flag_id Flag_id BIGINT NOT NULL ALTER TABLE CollectionMimeTypeRelation CHANGE Collection_id Collection_id BIGINT NOT NULL ALTER TABLE CollectionMimeTypeRelation CHANGE MimeType_id MimeType_id BIGINT NOT NULL ALTER TABLE CollectionPimItemRelation CHANGE Collection_id Collection_id BIGINT NOT NULL ALTER TABLE CollectionPimItemRelation CHANGE PimItem_id PimItem_id BIGINT NOT NULL UPDATE CollectionTable SET isVirtual = true WHERE resourceId IN (SELECT id FROM ResourceTable WHERE isVirtual = true) UPDATE CollectionTable SET isVirtual = 1 WHERE resourceId IN (SELECT id FROM ResourceTable WHERE isVirtual = 1) ALTER TABLE CollectionTable ALTER remoteId TYPE text USING convert_from(remoteId,'utf8'); ALTER TABLE CollectionTable ALTER remoteRevision TYPE text USING convert_from(remoteRevision,'utf8'); ALTER TABLE CollectionTable ALTER name TYPE text USING convert_from(name,'utf8'); ALTER TABLE CollectionTable ALTER cachePolicyLocalParts TYPE text USING convert_from(cachePolicyLocalParts,'utf8'); ALTER TABLE CollectionTable ALTER queryString TYPE text USING convert_from(queryString,'utf8'); ALTER TABLE CollectionTable ALTER queryLanguage TYPE text USING convert_from(queryLanguage,'utf8'); ALTER TABLE FlagTable ALTER name TYPE text USING convert_from(name,'utf8'); ALTER TABLE MimeTypeTable ALTER name TYPE text USING convert_from(name,'utf8'); ALTER TABLE PartTable ALTER name TYPE text USING convert_from(name,'utf8'); ALTER TABLE PimItemTable ALTER remoteId TYPE text USING convert_from(remoteId,'utf8'); ALTER TABLE PimItemTable ALTER remoteRevision TYPE text USING convert_from(remoteRevision,'utf8'); ALTER TABLE ResourceTable ALTER name TYPE text USING convert_from(name,'utf8'); UPDATE CollectionTable SET queryAttributes = 'QUERYLANGUAGE SPARQL' WHERE queryLanguage = 'SPARQL'; ALTER TABLE CollectionTable DROP COLUMN queryLanguage; UPDATE CollectionTable SET enabled = subscribed; ALTER TABLE CollectionTable DROP COLUMN subscribed; DELETE FROM PimItemFlagRelation WHERE pimItem_id IN ( SELECT pimItem_id FROM PimItemFlagRelation LEFT JOIN PimItemTable ON PimItemFlagRelation.pimItem_id = PimItemTable.id WHERE PimItemTable.id IS NULL) DELETE FROM PimItemFlagRelation WHERE flag_id IN ( SELECT flag_id FROM PimItemFlagRelation LEFT JOIN FlagTable ON PimItemFlagRelation.flag_id = FlagTable.id WHERE FlagTable.id IS NULL) DELETE FROM PimItemTagRelation WHERE pimItem_id IN ( SELECT pimItem_id FROM PimItemTagRelation LEFT JOIN PimItemTable ON PimItemTagRelation.pimItem_id = PimItemTable.id WHERE PimItemTable.id IS NULL) DELETE FROM PimItemTagRelation WHERE tag_id IN ( SELECT tag_id FROM PimItemTagRelation LEFT JOIN TagTable ON PimItemTagRelation.tag_id = TagTable.id WHERE TagTable.id IS NULL) DELETE FROM CollectionMimeTypeRelation WHERE collection_id IN ( SELECT collection_id FROM CollectionMimeTypeRelation LEFT JOIN CollectionTable ON CollectionMimeTypeRelation.collection_id = CollectionTable.id WHERE CollectionTable.id IS NULL) DELETE FROM CollectionMimeTypeRelation WHERE mimeType_id IN ( SELECT mimeType_id FROM CollectionMimeTypeRelation LEFT JOIN MimeTypeTable ON CollectionMimeTypeRelation.mimeType_id = MimeTypeTable.id WHERE MimeTypeTable.id IS NULL) DELETE FROM CollectionPimItemRelation WHERE collection_id IN ( SELECT collection_id FROM CollectionPimItemRelation LEFT JOIN CollectionTable ON CollectionPimItemRelation.collection_id = CollectionTable.id WHERE CollectionTable.id IS NULL) DELETE FROM CollectionPimItemRelation WHERE pimItem_id IN ( SELECT pimItem_id FROM CollectionPimItemRelation LEFT JOIN PimItemTable ON CollectionPimItemRelation.pimItem_id = PimItemTable.id WHERE PimItemTable.id IS NULL) DELETE FROM PimItemFlagRelation WHERE pimItem_id IN ( SELECT id FROM ( SELECT pimItem_id AS id FROM PimItemFlagRelation LEFT JOIN PimItemTable ON PimItemFlagRelation.pimItem_id = PimItemTable.id WHERE PimItemTable.id IS NULL) x) DELETE FROM PimItemFlagRelation WHERE flag_id IN ( SELECT id FROM ( SELECT flag_id AS id FROM PimItemFlagRelation LEFT JOIN FlagTable ON PimItemFlagRelation.flag_id = FlagTable.id WHERE FlagTable.id IS NULL) x) DELETE FROM PimItemTagRelation WHERE pimItem_id IN ( SELECT id FROM ( SELECT pimItem_id AS id FROM PimItemTagRelation LEFT JOIN PimItemTable ON PimItemTagRelation.pimItem_id = PimItemTable.id WHERE PimItemTable.id IS NULL) x) DELETE FROM PimItemTagRelation WHERE tag_id IN ( SELECT id FROM ( SELECT tag_id AS id FROM PimItemTagRelation LEFT JOIN TagTable ON PimItemTagRelation.tag_id = TagTable.id WHERE TagTable.id IS NULL) x) DELETE FROM CollectionMimeTypeRelation WHERE collection_id IN ( SELECT id FROM ( SELECT collection_id AS id FROM CollectionMimeTypeRelation LEFT JOIN CollectionTable ON CollectionMimeTypeRelation.collection_id = CollectionTable.id WHERE CollectionTable.id IS NULL) x) DELETE FROM CollectionMimeTypeRelation WHERE mimeType_id IN ( SELECT id FROM ( SELECT mimeType_id AS id FROM CollectionMimeTypeRelation LEFT JOIN MimeTypeTable ON CollectionMimeTypeRelation.mimeType_id = MimeTypeTable.id WHERE MimeTypeTable.id IS NULL) x) DELETE FROM CollectionPimItemRelation WHERE collection_id IN ( SELECT id FROM ( SELECT collection_id AS id FROM CollectionPimItemRelation LEFT JOIN CollectionTable ON CollectionPimItemRelation.collection_id = CollectionTable.id WHERE CollectionTable.id IS NULL) x) DELETE FROM CollectionPimItemRelation WHERE pimItem_id IN ( SELECT id FROM ( SELECT pimItem_id AS id FROM CollectionPimItemRelation LEFT JOIN PimItemTable ON CollectionPimItemRelation.pimItem_id = PimItemTable.id WHERE PimItemTable.id IS NULL) x) SELECT setval('tagtypetable_id_seq', (SELECT max(id) FROM TagTypeTable)) SELECT setval('relationtypetable_id_seq', (SELECT max(id) FROM RelationTypeTable)) UPDATE PartTable SET storage = external; ALTER TABLE PartTable DROP COLUMN external; UPDATE PartTable SET storage = cast(external as integer); ALTER TABLE PartTable DROP COLUMN external; UPDATE TagRemoteIdResourceRelationTable SET remoteId = printf('%s', remoteId) ALTER TABLE TagTable MODIFY COLUMN parentId BIGINT(20); ALTER TABLE TagTable ALTER COLUMN parentId DROP DEFAULT; + + + + ALTER TABLE PimItemTable ROW_FORMAT=DYNAMIC + ALTER TABLE PimItemTable MODIFY COLUMN remoteId VARBINARY(1024) + diff --git a/src/server/storage/mysql-global-mobile.conf b/src/server/storage/mysql-global-mobile.conf index bdaa6e2dd..19649d347 100644 --- a/src/server/storage/mysql-global-mobile.conf +++ b/src/server/storage/mysql-global-mobile.conf @@ -1,104 +1,107 @@ # # Global Akonadi MySQL server settings, # These settings can be adjusted using $HOME/.config/akonadi/mysql-local.conf # # Based on advice by Kris Köhntopp # [mysqld] # strict query parsing/interpretation # TODO: make Akonadi work with those settings enabled # sql_mode=strict_trans_tables,strict_all_tables,strict_error_for_division_by_zero,no_auto_create_user,no_auto_value_on_zero,no_engine_substitution,no_zero_date,no_zero_in_date,only_full_group_by,pipes_as_concat # sql_mode=strict_trans_tables # DEBUGGING: # log all queries, useful for debugging but generates an enormous amount of data # log=mysql.full # log queries slower than n seconds, log file name relative to datadir (for debugging only) # log_slow_queries=mysql.slow # long_query_time=1 # log queries not using indices, debug only, disable for production use # log_queries_not_using_indexes=1 # # mesure database size and adjust innodb_buffer_pool_size # SELECT sum(data_length) as bla, sum(index_length) as blub FROM information_schema.tables WHERE table_schema not in ("mysql", "information_schema"); # NOTES: # Keep Innob_log_waits and keep Innodb_buffer_pool_wait_free small (see show global status like "inno%", show global variables) #expire_logs_days=3 #sync_bin_log=0 # Use UTF-8 encoding for tables character_set_server=utf8 collation_server=utf8_general_ci # use InnoDB for transactions and better crash recovery default_storage_engine=innodb # memory pool InnoDB uses to store data dictionary information and other internal data structures (default:1M) # Deprecated in MySQL >= 5.6.3 innodb_additional_mem_pool_size=1M # memory buffer InnoDB uses to cache data and indexes of its tables (default:128M) # Larger values means less I/O innodb_buffer_pool_size=8M # Create a .ibd file for each table (default:0) innodb_file_per_table=1 # Write out the log buffer to the log file at each commit (default:1) innodb_flush_log_at_trx_commit=2 # Buffer size used to write to the log files on disk (default:1M for builtin, 8M for plugin) # larger values means less I/O innodb_log_buffer_size=1M # Size of each log file in a log group (default:5M) larger means less I/O but more time for recovery. innodb_log_file_size=2M +# Enable varchar index keys up to 3072 bytes (1024 characters), compared to 768 bytes (255 characters) with normal settings +innodb_large_prefix=1 + # # error log file name, relative to datadir (default:hostname.err) log_error=mysql.err # print warnings and connection errors (default:1) log_warnings=2 # Convert table named to lowercase lower_case_table_names=1 # Maximum size of one packet or any generated/intermediate string. (default:1M) max_allowed_packet=32M # Maximum simultaneous connections allowed (default:100) max_connections=256 # The two options below make no sense with prepared statements and/or transactions # (make sense when having the same query multiple times) # Memory allocated for caching query results (default:0 (disabled)) query_cache_size=0 # Do not cache results (default:1) query_cache_type=0 # Do not use the privileges mechanisms skip_grant_tables # Do not listen for TCP/IP connections at all skip_networking # The number of open tables for all threads. (default:64) table_open_cache=200 # How many threads the server should cache for reuse (default:0) thread_cache_size=3 # wait 365d before dropping the DB connection (default:8h) wait_timeout=31536000 # We use InnoDB, so don't let MyISAM eat up memory key_buffer_size=16K [client] default-character-set=utf8