diff options
Diffstat (limited to 'src/util/db/dbconnection.cpp')
-rw-r--r-- | src/util/db/dbconnection.cpp | 418 |
1 files changed, 418 insertions, 0 deletions
diff --git a/src/util/db/dbconnection.cpp b/src/util/db/dbconnection.cpp new file mode 100644 index 0000000000..7307bfeb18 --- /dev/null +++ b/src/util/db/dbconnection.cpp @@ -0,0 +1,418 @@ +#include "util/db/dbconnection.h" + +#include <QDir> +#include <QSqlDriver> +#include <QSqlError> + +#ifdef __SQLITE3__ +#include <sqlite3.h> +#endif // __SQLITE3__ + +#include "util/string.h" +#include "util/assert.h" + + +// Originally from public domain code: +// http://www.archivum.info/qt-interest@trolltech.com/2008-12/00584/Re-%28Qt-interest%29-Qt-Sqlite-UserDefinedFunction.html + +namespace { + +const QString kDatabaseType = "QSQLITE"; + +const QString kDatabaseHostName = "localhost"; +const QString kDatabaseFileName = "mixxxdb.sqlite"; +const QString kDatabaseUserName = "mixxx"; +const QString kDatabasePassword = "mixxx"; + +void makeLatinLow(QChar* c, int count) { + for (int i = 0; i < count; ++i) { + if (c[i].decompositionTag() != QChar::NoDecomposition) { + c[i] = c[i].decomposition()[0]; + } + if (c[i].isUpper()) { + c[i] = c[i].toLower(); + } + } +} + +const QChar LIKE_MATCH_ONE = '_'; +const QChar LIKE_MATCH_ALL = '%'; +const QChar LIKE_DEFAULT_ESCAPE = '\0'; + +// The collating function callback is invoked with a copy of the pArg +// application data pointer and with two strings in the encoding specified +// by the eTextRep argument. +// The collating function must return an integer that is negative, zero, +// or positive if the first string is less than, equal to, or greater +// than the second, respectively. +int sqliteStringCompareUTF16(void* pArg, + int len1, const void* data1, + int len2, const void* data2) { + Q_UNUSED(pArg); + // Construct a QString without copy + QString string1 = QString::fromRawData(reinterpret_cast<const QChar*>(data1), + len1 / sizeof(QChar)); + QString string2 = QString::fromRawData(reinterpret_cast<const QChar*>(data2), + len2 / sizeof(QChar)); + return compareLocalAwareCaseInsensitive(string1, string2); +} + +// Compare two strings for equality where the first string is +// a "LIKE" expression. Return true (1) if they are the same and +// false (0) if they are different. +// This is the original sqlite3 icuLikeCompare rewritten for QChar +int likeCompareInner( + const QChar* pattern, // LIKE pattern + int patternSize, + const QChar* string, // The string to compare against + int stringSize, + const QChar esc) { // The escape character + + int iPattern = 0; // Current index in pattern + int iString = 0; // Current index in string + + bool prevEscape = false; // True if the previous character was uEsc + + while (iPattern < patternSize) { + // Read (and consume) the next character from the input pattern. + QChar uPattern = pattern[iPattern++]; + // There are now 4 possibilities: + // 1. uPattern is an unescaped match-all character "%", + // 2. uPattern is an unescaped match-one character "_", + // 3. uPattern is an unescaped escape character, or + // 4. uPattern is to be handled as an ordinary character + + if (!prevEscape && uPattern == LIKE_MATCH_ALL) { + // Case 1. + QChar c; + + // Skip any LIKE_MATCH_ALL or LIKE_MATCH_ONE characters that follow a + // LIKE_MATCH_ALL. For each LIKE_MATCH_ONE, skip one character in the + // test string. + + if (iPattern >= patternSize) { + // Tailing % + return 1; + } + + while ((c = pattern[iPattern]) == LIKE_MATCH_ALL || c == LIKE_MATCH_ONE) { + if (c == LIKE_MATCH_ONE) { + if (++iString == stringSize) { + return 0; + } + } + if (++iPattern == patternSize) { + // Two or more tailing % + return 1; + } + } + + while (iString < stringSize) { + if (likeCompareInner(&pattern[iPattern], patternSize - iPattern, + &string[iString], stringSize - iString, esc)) { + return 1; + } + iString++; + } + return 0; + } else if (!prevEscape && uPattern == LIKE_MATCH_ONE) { + // Case 2. + if (++iString == stringSize) { + return 0; + } + } else if (!prevEscape && uPattern == esc) { + // Case 3. + prevEscape = 1; + } else { + // Case 4. + if (iString == stringSize) { + return 0; + } + QChar uString = string[iString++]; + if (uString != uPattern) { + return 0; + } + prevEscape = false; + } + } + return iString == stringSize; +} + +#ifdef __SQLITE3__ + +// This implements the like() SQL function. This is used by the LIKE operator. +// The SQL statement 'A LIKE B' is implemented as 'like(B, A)', and if there is +// an escape character, say E, it is implemented as 'like(B, A, E)' +//static +void sqliteLike(sqlite3_context *context, + int aArgc, + sqlite3_value **aArgv) { + DEBUG_ASSERT_AND_HANDLE(aArgc == 2 || aArgc == 3) { + return; + } + + const char* b = reinterpret_cast<const char*>( + sqlite3_value_text(aArgv[0])); + const char* a = reinterpret_cast<const char*>( + sqlite3_value_text(aArgv[1])); + + if (!a || !b) { + return; + } + + QString stringB = QString::fromUtf8(b); // Like String + QString stringA = QString::fromUtf8(a); + + QChar esc = LIKE_DEFAULT_ESCAPE; + if (aArgc == 3) { + const char* e = reinterpret_cast<const char*>( + sqlite3_value_text(aArgv[2])); + if (e) { + QString stringE = QString::fromUtf8(e); + if (!stringE.isEmpty()) { + esc = stringE.data()[0]; + } + } + } + + int ret = DbConnection::likeCompareLatinLow(&stringB, &stringA, esc); + sqlite3_result_int64(context, ret); + return; +} + +#endif // __SQLITE3__ + +} // anonymous namespace + +//static +const char* const DbConnection::kStringCollationFunc = "mixxxStringCollation"; + +DbConnection::DbConnection(const QString& dirPath) + : m_filePath(QDir(dirPath).filePath(kDatabaseFileName)), + m_database(QSqlDatabase::addDatabase(kDatabaseType)) { + qDebug() + << "Available drivers for database connection:" + << QSqlDatabase::drivers(); + + m_database.setHostName(kDatabaseHostName); + m_database.setDatabaseName(m_filePath); + m_database.setUserName(kDatabaseUserName); + m_database.setPassword(kDatabasePassword); + if (!m_database.open()) { + qWarning() << "Failed to open database connection:" + << *this + << m_database.lastError(); + DEBUG_ASSERT(!*this); // failure + return; // early exit + } + + QVariant v = m_database.driver()->handle(); + DEBUG_ASSERT_AND_HANDLE(v.isValid()) { + return; // early exit + } +#ifdef __SQLITE3__ + if (strcmp(v.typeName(), "sqlite3*") == 0) { + // v.data() returns a pointer to the handle + sqlite3* handle = *static_cast<sqlite3**>(v.data()); + DEBUG_ASSERT_AND_HANDLE(handle != nullptr) { + qWarning() << "Could not get sqlite3 handle"; + m_database.close(); + DEBUG_ASSERT(!*this); // failure + return; // early exit + } + + int result = sqlite3_create_collation( + handle, + kStringCollationFunc, + SQLITE_UTF16, + nullptr, + sqliteStringCompareUTF16); + DEBUG_ASSERT_AND_HANDLE(result == SQLITE_OK) { + qWarning() << "Failed to install string collation function:" << result; + } + + result = sqlite3_create_function( + handle, + "like", + 2, + SQLITE_ANY, + nullptr, + sqliteLike, + nullptr, nullptr); + DEBUG_ASSERT_AND_HANDLE(result == SQLITE_OK) { + qWarning() << "Failed to install like 2 function:" << result; + } + + result = sqlite3_create_function( + handle, + "like", + 3, + SQLITE_UTF8, // No conversion, Data is stored as UTF8 + nullptr, + sqliteLike, + nullptr, nullptr); + DEBUG_ASSERT_AND_HANDLE(result == SQLITE_OK) { + qWarning() << "Failed to install like 3 function:" << result; + } + + DEBUG_ASSERT(*this); // success + return; // early exit + } +#endif // __SQLITE3__ + qWarning() << "Unsupported database driver:" << v.typeName(); +} + +DbConnection::~DbConnection() { + DEBUG_ASSERT_AND_HANDLE(*this) { + qWarning() + << "Database connection has already been closed:" + << *this; + return; // early exit + } + // There should never be an outstanding transaction when this code is + // called. If there is, it means we probably aren't committing a + // transaction somewhere that should be. + DEBUG_ASSERT_AND_HANDLE(!m_database.rollback()) { + qWarning() + << "Rolled back open transaction before closing database connection:" + << *this; + } + DEBUG_ASSERT(*this); + qDebug() + << "Closing database connection:" + << *this; + m_database.close(); + DEBUG_ASSERT(!*this); +} + +QDebug operator<<(QDebug debug, const DbConnection& dbConnection) { + return debug << kDatabaseType << dbConnection.m_filePath; +} + +//static +int DbConnection::likeCompareLatinLow( + QString* pattern, + QString* string, + QChar esc) { + makeLatinLow(pattern->data(), pattern->length()); + makeLatinLow(string->data(), string->length()); + return likeCompareInner( + pattern->data(), pattern->length(), + string->data(), string->length(), + esc); +} + + +/* +static int +likeCompare(nsAString::const_iterator aPatternItr, + nsAString::const_iterator aPatternEnd, + nsAString::const_iterator aStringItr, + nsAString::const_iterator aStringEnd, + PRUnichar aEscape) +{ + const PRUnichar LIKE_MATCH_ALL('%'); + const PRUnichar LIKE_MATCH_ONE('_'); + + PRBool lastWasEscape = PR_FALSE; + while (aPatternItr != aPatternEnd) { + +* What we do in here is take a look at each character from the input +* pattern, and do something with it. There are 4 possibilities: +* 1) character is an un-escaped match-all character +* 2) character is an un-escaped match-one character +* 3) character is an un-escaped escape character +* 4) character is not any of the above + + if (!lastWasEscape && *aPatternItr == LIKE_MATCH_ALL) { + // CASE 1 + +* Now we need to skip any LIKE_MATCH_ALL or LIKE_MATCH_ONE characters that follow a +* LIKE_MATCH_ALL character. For each LIKE_MATCH_ONE character, skip one character +* in the pattern string. + + while (*aPatternItr == LIKE_MATCH_ALL || *aPatternItr == LIKE_MATCH_ONE) { + if (*aPatternItr == LIKE_MATCH_ONE) { + // If we've hit the end of the string we are testing, no match + if (aStringItr == aStringEnd) + return 0; + aStringItr++; + } + aPatternItr++; + } + + // If we've hit the end of the pattern string, match + if (aPatternItr == aPatternEnd) + return 1; + + while (aStringItr != aStringEnd) { + if (likeCompare(aPatternItr, aPatternEnd, aStringItr, aStringEnd, aEscape)) { + // we've hit a match, so indicate this + return 1; + } + aStringItr++; + } + + // No match + return 0; + } else if (!lastWasEscape && *aPatternItr == LIKE_MATCH_ONE) { + // CASE 2 + if (aStringItr == aStringEnd) { + // If we've hit the end of the string we are testing, no match + return 0; + } + aStringItr++; + lastWasEscape = PR_FALSE; + } else if (!lastWasEscape && *aPatternItr == aEscape) { + // CASE 3 + lastWasEscape = PR_TRUE; + } else { + // CASE 4 + if (ToUpperCase(*aStringItr) != ToUpperCase(*aPatternItr)) { + // If we've hit a point where the strings don't match, there is no match + return 0; + } + aStringItr++; + lastWasEscape = PR_FALSE; + } + + aPatternItr++; + } + + return aStringItr == aStringEnd; +} + + +void +likeFunction(sqlite3_context *p, + int aArgc, + sqlite3_value **aArgv) +{ + NS_ASSERTION(2 == aArgc || 3 == aArgc, "Invalid number of arguments!"); + + if (sqlite3_value_bytes(aArgv[0]) > SQLITE_MAX_LIKE_PATTERN_LENGTH) { + sqlite3_result_error(p, "LIKE or GLOB pattern too complex", SQLITE_TOOBIG); + return; + } + + if (!sqlite3_value_text16(aArgv[0]) || !sqlite3_value_text16(aArgv[1])) + return; + + nsDependentString A(static_cast<const PRUnichar *>(sqlite3_value_text16(aArgv[1]))); + nsDependentString B(static_cast<const PRUnichar *>(sqlite3_value_text16(aArgv[0]))); + NS_ASSERTION(!B.IsEmpty(), "LIKE string must not be null!"); + + PRUnichar E = 0; + if (3 == aArgc) + E = static_cast<const PRUnichar *>(sqlite3_value_text16(aArgv[2]))[0]; + + nsAString::const_iterator itrString, endString; + A.BeginReading(itrString); + A.EndReading(endString); + nsAString::const_iterator itrPattern, endPattern; + B.BeginReading(itrPattern); + B.EndReading(endPattern); + sqlite3_result_int(p, likeCompare(itrPattern, endPattern, + itrString, endString, E)); +} +*/ |