From kde-commits Sat Jan 31 21:15:50 2015 From: =?utf-8?q?Christian_D=C3=A1vid?= Date: Sat, 31 Jan 2015 21:15:50 +0000 To: kde-commits Subject: [kmymoney] kmymoney/mymoney/storage: Added function highestIdNumString() to extract new ids from SQL Message-Id: X-MARC-Message: https://marc.info/?l=kde-commits&m=142273895923865 Git commit 35c53b901b155e3a917e37e7d3e6124ec4dead1b by Christian D=C3=A1vid= , on behalf of Daniel Ring. Committed on 31/01/2015 at 13:21. Pushed by christiand into branch 'master'. Added function highestIdNumString() to extract new ids from SQL tables This adds a function to retrieve the highest ID number from the database. The IDs are prefixed by a short identifier string, and are stored in the database as strings. CCBUG: 339103 REVIEW: 122138 M +13 -0 kmymoney/mymoney/storage/mymoneydatabasemgrtest.cpp M +1 -0 kmymoney/mymoney/storage/mymoneydatabasemgrtest.h M +26 -0 kmymoney/mymoney/storage/mymoneydbdriver.cpp M +5 -0 kmymoney/mymoney/storage/mymoneydbdriver.h M +12 -0 kmymoney/mymoney/storage/mymoneystoragesql.cpp M +9 -0 kmymoney/mymoney/storage/mymoneystoragesql.h http://commits.kde.org/kmymoney/35c53b901b155e3a917e37e7d3e6124ec4dead1b diff --git a/kmymoney/mymoney/storage/mymoneydatabasemgrtest.cpp b/kmymoney= /mymoney/storage/mymoneydatabasemgrtest.cpp index 642fc53..746e2a7 100644 --- a/kmymoney/mymoney/storage/mymoneydatabasemgrtest.cpp +++ b/kmymoney/mymoney/storage/mymoneydatabasemgrtest.cpp @@ -2412,3 +2412,16 @@ void MyMoneyDatabaseMgrTest::testRemoveOnlineJob() QVERIFY(m->dirty() =3D=3D false); } } + +void MyMoneyDatabaseMgrTest::testHighestIdNum() +{ + testAttachDb(); + + if (!m_canOpen) + QSKIP("Database test skipped because no database could be opened.", Sk= ipAll); + + testAddTransactions(); + + QCOMPARE(m->m_sql->highestIdNum(QLatin1String("kmmTransactions"), QLatin= 1String("id"), 1), 2ul); + QCOMPARE(m->m_sql->highestIdNum(QLatin1String("kmmAccounts"), QLatin1Str= ing("id"), 1), 6ul); +} diff --git a/kmymoney/mymoney/storage/mymoneydatabasemgrtest.h b/kmymoney/m= ymoney/storage/mymoneydatabasemgrtest.h index 07fc175..71b86d6 100644 --- a/kmymoney/mymoney/storage/mymoneydatabasemgrtest.h +++ b/kmymoney/mymoney/storage/mymoneydatabasemgrtest.h @@ -99,6 +99,7 @@ private slots: void testAddOnlineJob(); void testModifyOnlineJob(); void testRemoveOnlineJob(); + void testHighestIdNum(); }; = #endif diff --git a/kmymoney/mymoney/storage/mymoneydbdriver.cpp b/kmymoney/mymone= y/storage/mymoneydbdriver.cpp index ab74492..cec1221 100644 --- a/kmymoney/mymoney/storage/mymoneydbdriver.cpp +++ b/kmymoney/mymoney/storage/mymoneydbdriver.cpp @@ -66,6 +66,7 @@ public: virtual const QString intString(const MyMoneyDbIntColumn& c) const; virtual const QString timestampString(const MyMoneyDbDatetimeColumn& c) = const; virtual const QString tableOptionString() const; + virtual const QString highestIdNumString(const QString& tableName, const= QString& tableField, const int prefixLength) const; virtual const QStringList tables(QSql::TableType tt, const QSqlDatabase&= db) const; }; = @@ -79,6 +80,7 @@ public: virtual const QString modifyColumnString(const QString& tableName, const= QString& columnName, const MyMoneyDbColumn& newDef) const; virtual const QString intString(const MyMoneyDbIntColumn& c) const; virtual const QString textString(const MyMoneyDbTextColumn& c) const; + virtual const QString highestIdNumString(const QString& tableName, const= QString& tableField, const int prefixLength) const; }; = class MyMoneyODBCDriver : public MyMoneyDbDriver @@ -104,6 +106,7 @@ public: virtual const QString modifyColumnString(const QString& tableName, const= QString& columnName, const MyMoneyDbColumn& newDef) const; virtual const QString intString(const MyMoneyDbIntColumn& c) const; virtual const QString textString(const MyMoneyDbTextColumn& c) const; + virtual const QString highestIdNumString(const QString& tableName, const= QString& tableField, const int prefixLength) const; }; = class MyMoneySybaseDriver : public MyMoneyDbDriver @@ -630,6 +633,29 @@ const QString MyMoneyDbDriver::tableOptionString() con= st return ""; } = +//*********************************************** +// Define the highestIdNum string +// PostgreSQL and Oracle return errors when a non-numerical string is cast= to an integer, so a regex is used to skip strings that aren't entirely num= erical after the prefix is removed +const QString MyMoneyDbDriver::highestIdNumString(const QString& tableName= , const QString& tableField, const int prefixLength) const +{ + return QString("SELECT MAX(CAST(SUBSTR(%1, %2) AS INTEGER)) FROM %3;").a= rg(tableField).arg(prefixLength + 1).arg(tableName); +} + +const QString MyMoneyMysqlDriver::highestIdNumString(const QString& tableN= ame, const QString& tableField, const int prefixLength) const +{ + return QString("SELECT MAX(CAST(SUBSTR(%1, %2) AS UNSIGNED INTEGER)) FRO= M %3;").arg(tableField).arg(prefixLength + 1).arg(tableName); +} + +const QString MyMoneyPostgresqlDriver::highestIdNumString(const QString& t= ableName, const QString& tableField, const int prefixLength) const +{ + return QString("SELECT MAX(CAST(SUBSTR(%1, %2) AS INTEGER)) FROM %3 WHER= E SUBSTR(%1, %2) ~ '^[0-9]+$';").arg(tableField).arg(prefixLength + 1).arg(= tableName); +} + +const QString MyMoneyOracleDriver::highestIdNumString(const QString& table= Name, const QString& tableField, const int prefixLength) const +{ + return QString("SELECT MAX(TO_NUMBER(SUBSTR(%1, %2))) FROM %3 WHERE REGE= XP_LIKE(SUBSTR(%1, %2), '^[0-9]+$');").arg(tableField).arg(prefixLength + 1= ).arg(tableName); +} + //************************************************* // Define bool MyMoneyDbDriver::isPasswordSupported() const diff --git a/kmymoney/mymoney/storage/mymoneydbdriver.h b/kmymoney/mymoney/= storage/mymoneydbdriver.h index e5fe0e9..b0171b6 100644 --- a/kmymoney/mymoney/storage/mymoneydbdriver.h +++ b/kmymoney/mymoney/storage/mymoneydbdriver.h @@ -159,6 +159,11 @@ public: virtual const QString tableOptionString() const; = /** + * @return The SQL string to find the highest ID number with an arbitrar= y prefix + */ + virtual const QString highestIdNumString(const QString& tableName, const= QString& tableField, const int prefixLength) const; + + /** * Override standard tables() call for bug 252841 */ virtual const QStringList tables(QSql::TableType tt, const QSqlDatabase&= db) const; diff --git a/kmymoney/mymoney/storage/mymoneystoragesql.cpp b/kmymoney/mymo= ney/storage/mymoneystoragesql.cpp index fa9fbdf..ff10f38 100644 --- a/kmymoney/mymoney/storage/mymoneystoragesql.cpp +++ b/kmymoney/mymoney/storage/mymoneystoragesql.cpp @@ -895,6 +895,18 @@ bool MyMoneyStorageSql::writeFile(void) return false; } } + +long unsigned MyMoneyStorageSql::highestIdNum(QString tableName, QString t= ableField, int prefixLength) +{ + MyMoneyDbTransaction t(*this, Q_FUNC_INFO); + QSqlQuery q(*this); + + if (!q.exec(m_driver->highestIdNumString(tableName, tableField, prefixLe= ngth)) || !q.next()) + throw MYMONEYEXCEPTION(buildError(q, Q_FUNC_INFO, QString("retrieving = highest ID number"))); // krazy:exclude=3Dcrashy + + return q.value(0).toULongLong(); +} + // --------------- SQL Transaction (commit unit) handling ----------------= ------------------- void MyMoneyStorageSql::startCommitUnit(const QString& callingFunction) { diff --git a/kmymoney/mymoney/storage/mymoneystoragesql.h b/kmymoney/mymone= y/storage/mymoneystoragesql.h index 469c0f4..f85ab1b 100644 --- a/kmymoney/mymoney/storage/mymoneystoragesql.h +++ b/kmymoney/mymoney/storage/mymoneystoragesql.h @@ -180,6 +180,15 @@ public: const QString& lastError() const { return (m_error); }; + + /** + * MyMoneyStorageSql get highest ID number from the database + * + * @return : highest ID number + * + */ + long unsigned highestIdNum(QString tableName, QString tableField, int pr= efixLength); + /** * This method is used when a database file is open, and the data is to * be saved in a different file or format. It will ensure that all data