summaryrefslogtreecommitdiff
authorzecke <zecke>2005-06-27 17:47:58 (UTC)
committer zecke <zecke>2005-06-27 17:47:58 (UTC)
commitcc4771f193215827036689291d9d3d74d2cbef3f (patch) (side-by-side diff)
tree6e9e1e7ce5da4408ce8984574772079c6a97cf46
parent3e1e2a8751a169b553570a585293dd9ea6de41cd (diff)
downloadopie-cc4771f193215827036689291d9d3d74d2cbef3f.zip
opie-cc4771f193215827036689291d9d3d74d2cbef3f.tar.gz
opie-cc4771f193215827036689291d9d3d74d2cbef3f.tar.bz2
Simplify SQL query to not need a temporary SQL table
Patch courtsey Marcin Juszkiewicz (okayed by eilers)
Diffstat (more/less context) (ignore whitespace changes)
-rw-r--r--libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp13
1 files changed, 2 insertions, 11 deletions
diff --git a/libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp b/libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp
index 175d62a..6aaa14c 100644
--- a/libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp
+++ b/libopie2/opiepim/backend/ocontactaccessbackend_sql.cpp
@@ -563,68 +563,66 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist,
QMap<QString, int> translate = OPimContactFields::untrFieldsToId();
// Convert every filled field to a SQL-Query
// bool isAnyFieldSelected = false;
for ( QStringList::Iterator it = ++fieldList.begin(); it != fieldList.end(); ++it ){
int id = translate[*it];
QString queryStr = queryFields[id];
QDate* endDate = 0l;
if ( !queryStr.isEmpty() ){
// If something is alredy stored in the query, add an "AND"
// to the end of the string to prepare for the next ..
if ( !searchQuery.isEmpty() )
searchQuery += " AND";
// isAnyFieldSelected = true;
switch( id ){
case Qtopia::Birthday:
endDate = new QDate( query.birthday() );
// Fall through !
case Qtopia::Anniversary:
if ( endDate == 0l )
endDate = new QDate( query.anniversary() );
if ( settings & OPimContactAccess::DateDiff ) {
// To handle datediffs correctly, we need to remove the year information from
// the birthday and anniversary.
// To do this efficiently, we will create a temporary table which contains the
// information we need and do the query on it.
// This table is just visible for this process and will be removed
// automatically after using.
- datediff_query = "CREATE TEMP TABLE bs ( uid, \"Birthday\", \"Anniversary\" );";
- datediff_query += "INSERT INTO bs SELECT uid,substr(\"Birthday\", 6, 10),substr(\"Anniversary\", 6, 10) FROM addressbook WHERE ( \"Birthday\" != '' OR \"Anniversary\" != '' );";
- datediff_query += QString( "SELECT uid FROM bs WHERE " ) + uid_query;
- datediff_query += QString( " (\"%1\" <= '%2-%3\' AND \"%4\" >= '%5-%6')" )
+ datediff_query = "SELECT uid,substr(\"Birthday\", 6, 10) as \"BirthdayMD\", substr(\"Anniversary\", 6, 10) as \"AnniversaryMD\" FROM addressbook WHERE ( \"Birthday\" != '' OR \"Anniversary\" != '' ) AND ";
+ datediff_query += QString( " (\"%1MD\" <= '%2-%3\' AND \"%4MD\" >= '%5-%6')" )
.arg( *it )
//.arg( QString::number( endDate->year() ).rightJustify( 4, '0' ) )
.arg( QString::number( endDate->month() ).rightJustify( 2, '0' ) )
.arg( QString::number( endDate->day() ).rightJustify( 2, '0' ) )
.arg( *it )
//.arg( QString::number( startDate.year() ).rightJustify( 4, '0' ) )
.arg( QString::number( startDate.month() ).rightJustify( 2, '0' ) )
.arg( QString::number( startDate.day() ).rightJustify( 2, '0' ) ) ;
}
if ( settings & OPimContactAccess::DateYear ){
searchQuery += QString( " (\"%1\" LIKE '%2-%')" )
.arg( *it )
.arg( QString::number( endDate->year() ).rightJustify( 4, '0' ) );
}
if ( settings & OPimContactAccess::DateMonth ){
if ( settings & OPimContactAccess::DateYear )
searchQuery += " AND";
searchQuery += QString( " (\"%1\" LIKE '%-%2-%')" )
.arg( *it )
.arg( QString::number( endDate->month() ).rightJustify( 2, '0' ) );
}
if ( settings & OPimContactAccess::DateDay ){
if ( ( settings & OPimContactAccess::DateYear )
|| ( settings & OPimContactAccess::DateMonth ) )
searchQuery += " AND";
searchQuery += QString( " (\"%1\" LIKE '%-%-%2')" )
.arg( *it )
@@ -655,71 +653,64 @@ UIDArray OPimContactAccessBackend_SQL::queryByExample ( const UIDArray& uidlist,
searchQuery.remove( searchQuery.length()-3, 3 ); // Hmmmm..
}
}
// Now compose the complete query
QString qu = "SELECT uid FROM addressbook WHERE " + uid_query;
if ( !datediff_query.isEmpty() && !searchQuery.isEmpty() ){
// If we use DateDiff, we have to intersect two queries.
qu = datediff_query + QString( " INTERSECT " ) + qu + searchQuery;
} else if ( datediff_query.isEmpty() && !searchQuery.isEmpty() ){
qu += searchQuery;
} else if ( !datediff_query.isEmpty() && searchQuery.isEmpty() ){
qu = datediff_query;
} else if ( datediff_query.isEmpty() && searchQuery.isEmpty() ){
UIDArray empty;
return empty;
}
odebug << "queryByExample query: " << qu << "" << oendl;
// Execute query and return the received uid's
OSQLRawQuery raw( qu );
OSQLResult res = m_driver->query( &raw );
if ( res.state() != OSQLResult::Success ){
UIDArray empty;
return empty;
}
UIDArray list = extractUids( res );
- // Remove temp table if created
- if ( !datediff_query.isEmpty( ) ){
- qu = "DROP TABLE bs";
- OSQLRawQuery raw( qu );
- OSQLResult res = m_driver->query( &raw );
- }
-
return list;
}
UIDArray OPimContactAccessBackend_SQL::matchRegexp( const QRegExp &r ) const
{
#if 0
QArray<int> nix(0);
return nix;
#else
QString qu = "SELECT uid FROM addressbook WHERE (";
QString searchlist;
QStringList fieldList = OPimContactFields::untrfields( false );
// QMap<QString, int> translate = OPimContactFields::untrFieldsToId();
for ( QStringList::Iterator it = ++fieldList.begin(); it != fieldList.end(); ++it ){
if ( !searchlist.isEmpty() )
searchlist += " OR ";
searchlist += " rlike(\""+ r.pattern() + "\",\"" + *it + "\") ";
}
qu = qu + searchlist + ")";
odebug << "query: " << qu << "" << oendl;
OSQLRawQuery raw( qu );
OSQLResult res = m_driver->query( &raw );
return extractUids( res );
#endif