[prev in list] [next in list] [prev in thread] [next in thread]
List: kde-commits
Subject: [kmymoney] kmymoney/mymoney/storage: Added function highestIdNumString() to extract new ids from SQL
From: Christian_Dávid <christian-david () web ! de>
Date: 2015-01-31 21:15:50
Message-ID: E1YHfOQ-0000jm-7w () scm ! kde ! org
[Download RAW message or body]
Git commit 35c53b901b155e3a917e37e7d3e6124ec4dead1b by Christian Dávid, 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() == false);
}
}
+
+void MyMoneyDatabaseMgrTest::testHighestIdNum()
+{
+ testAttachDb();
+
+ if (!m_canOpen)
+ QSKIP("Database test skipped because no database could be opened.", SkipAll);
+
+ testAddTransactions();
+
+ QCOMPARE(m->m_sql->highestIdNum(QLatin1String("kmmTransactions"), \
QLatin1String("id"), 1), 2ul); + \
QCOMPARE(m->m_sql->highestIdNum(QLatin1String("kmmAccounts"), QLatin1String("id"), \
1), 6ul); +}
diff --git a/kmymoney/mymoney/storage/mymoneydatabasemgrtest.h \
b/kmymoney/mymoney/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/mymoney/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() const
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 numerical 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;").arg(tableField).arg(prefixLength + 1).arg(tableName); +}
+
+const QString MyMoneyMysqlDriver::highestIdNumString(const QString& tableName, const \
QString& tableField, const int prefixLength) const +{
+ return QString("SELECT MAX(CAST(SUBSTR(%1, %2) AS UNSIGNED INTEGER)) FROM \
%3;").arg(tableField).arg(prefixLength + 1).arg(tableName); +}
+
+const QString MyMoneyPostgresqlDriver::highestIdNumString(const QString& tableName, \
const QString& tableField, const int prefixLength) const +{
+ return QString("SELECT MAX(CAST(SUBSTR(%1, %2) AS INTEGER)) FROM %3 WHERE \
SUBSTR(%1, %2) ~ '^[0-9]+$';").arg(tableField).arg(prefixLength + 1).arg(tableName); \
+} +
+const QString MyMoneyOracleDriver::highestIdNumString(const QString& tableName, \
const QString& tableField, const int prefixLength) const +{
+ return QString("SELECT MAX(TO_NUMBER(SUBSTR(%1, %2))) FROM %3 WHERE \
REGEXP_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 arbitrary 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/mymoney/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 tableField, \
int prefixLength) +{
+ MyMoneyDbTransaction t(*this, Q_FUNC_INFO);
+ QSqlQuery q(*this);
+
+ if (!q.exec(m_driver->highestIdNumString(tableName, tableField, prefixLength)) || \
!q.next()) + throw MYMONEYEXCEPTION(buildError(q, Q_FUNC_INFO, QString("retrieving \
highest ID number"))); // krazy:exclude=crashy +
+ 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/mymoney/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 \
prefixLength); +
/**
* 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
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic