r18135 - gnucash/trunk/src/backend/sql - Implement QofQuery for splits. At this point, the queries used by current reports work, but
Phil Longstaff
plongstaff at code.gnucash.org
Fri Jun 19 12:48:49 EDT 2009
Author: plongstaff
Date: 2009-06-19 12:48:48 -0400 (Fri, 19 Jun 2009)
New Revision: 18135
Trac: http://svn.gnucash.org/trac/changeset/18135
Added:
gnucash/trunk/src/backend/sql/escape.c
gnucash/trunk/src/backend/sql/escape.h
Modified:
gnucash/trunk/src/backend/sql/Makefile.am
gnucash/trunk/src/backend/sql/gnc-backend-sql.c
gnucash/trunk/src/backend/sql/gnc-backend-sql.h
gnucash/trunk/src/backend/sql/gnc-transaction-sql.c
Log:
Implement QofQuery for splits. At this point, the queries used by current reports work, but
more split fields could be implemented. Implemented are: account.guid, reconcile-flag,
trans.desc and trans.date-posted.
Fixes Bug #585167
Modified: gnucash/trunk/src/backend/sql/Makefile.am
===================================================================
--- gnucash/trunk/src/backend/sql/Makefile.am 2009-06-15 22:19:35 UTC (rev 18134)
+++ gnucash/trunk/src/backend/sql/Makefile.am 2009-06-19 16:48:48 UTC (rev 18135)
@@ -26,7 +26,8 @@
gnc-recurrence-sql.c \
gnc-schedxaction-sql.c \
gnc-slots-sql.c \
- gnc-transaction-sql.c
+ gnc-transaction-sql.c \
+ escape.c
noinst_HEADERS = \
gnc-account-sql.h \
@@ -39,7 +40,8 @@
gnc-recurrence-sql.h \
gnc-schedxaction-sql.h \
gnc-slots-sql.h \
- gnc-transaction-sql.h
+ gnc-transaction-sql.h \
+ escape.h
libgnc_backend_sql_la_LIBADD = \
${GLIB_LIBS} \
Added: gnucash/trunk/src/backend/sql/escape.c
===================================================================
--- gnucash/trunk/src/backend/sql/escape.c (rev 0)
+++ gnucash/trunk/src/backend/sql/escape.c 2009-06-19 16:48:48 UTC (rev 18135)
@@ -0,0 +1,150 @@
+/********************************************************************\
+ * escape.c : escape SQL reserved characters *
+ * Copyright (C) 2001 Linas Vepstas <linas at linas.org> *
+ * *
+ * This program is free software; you can redistribute it and/or *
+ * modify it under the terms of the GNU 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 General Public License*
+ * along with this program; if not, contact: *
+ * *
+ * Free Software Foundation Voice: +1-617-542-5942 *
+ * 51 Franklin Street, Fifth Floor Fax: +1-617-542-2652 *
+ * Boston, MA 02110-1301, USA gnu at gnu.org *
+\********************************************************************/
+
+/*
+ * FILE:
+ * esacpe.c
+ *
+ * FUNCTION:
+ * Escapes the ' and \ characters in a string
+ */
+
+#include "config.h"
+#include <glib.h>
+#include <string.h>
+
+#include "gnc-engine.h"
+#include "escape.h"
+
+static QofLogModule log_module = GNC_MOD_BACKEND;
+
+/* ================================================ */
+
+struct _escape {
+ /* pointer to memory used for escaping arguments */
+ char * escape;
+ size_t esc_buflen;
+};
+
+/* ================================================ */
+/* escape single-quote marks and backslashes so that the
+ * database SQL parser doesn't puke on the query string
+ */
+
+const char *
+sqlEscapeString (sqlEscape *b, const char *str)
+{
+ const char *p, *src_head;
+ char *dst_tail;
+ size_t len, slen;
+
+ ENTER("str = %s", str);
+
+ if (!b || !str) { LEAVE("(null) args"); return NULL; }
+
+ /* if a string is escaped twice, just return the first */
+ if (b->escape == str) {
+ LEAVE("%s: already escaped", str);
+ return str;
+ }
+
+ /* if nothing to escape, just return */
+ len = strlen (str);
+ slen = strcspn (str, "\\\'");
+ if (len == slen) {
+ LEAVE("nothing to escape");
+ return str;
+ }
+
+ /* count to see how much space we'll need */
+ p = str + slen + 1;
+ while (*p)
+ {
+ len ++;
+ p += 1 + strcspn (p, "\\\'");
+ }
+
+ /* get more space, if needed */
+ if (len >= b->esc_buflen)
+ {
+ b->escape = g_realloc(b->escape, len+100);
+ b->esc_buflen = len+100;
+ }
+
+ /* copy and escape */
+ src_head = (char *) str;
+ dst_tail = b->escape;
+ p = src_head + strcspn (src_head, "\\\'");
+ while (*p)
+ {
+ size_t cp_len = p - src_head;
+
+ strncpy (dst_tail, src_head, cp_len);
+ dst_tail += cp_len;
+ *dst_tail = '\\';
+ dst_tail ++;
+ *dst_tail = *p;
+ dst_tail ++;
+
+ src_head = p+1;
+ p = src_head + strcspn (src_head, "\\\'");
+ }
+ if (p != src_head)
+ {
+ size_t cp_len = p - src_head;
+
+ strncpy (dst_tail, src_head, cp_len);
+ dst_tail += cp_len;
+ }
+ *dst_tail = 0;
+
+ LEAVE("b->escape = %s", b->escape);
+ return b->escape;
+}
+
+/* ================================================ */
+
+#define INITIAL_BUFSZ 2000
+
+sqlEscape *
+sqlEscape_new (void)
+{
+ sqlEscape *b = g_new (sqlEscape, 1);
+
+ b->escape = g_malloc (INITIAL_BUFSZ);
+ b->esc_buflen = INITIAL_BUFSZ;
+ return (b);
+}
+
+/* ================================================ */
+
+void
+sqlEscape_destroy (sqlEscape *b)
+{
+ ENTER(" ");
+ if (!b) { LEAVE("b is (null)"); return; }
+ g_free (b->escape); b->escape = NULL;
+ g_free (b);
+ LEAVE(" ");
+}
+
+/* ================ END OF FILE ==================== */
Added: gnucash/trunk/src/backend/sql/escape.h
===================================================================
--- gnucash/trunk/src/backend/sql/escape.h (rev 0)
+++ gnucash/trunk/src/backend/sql/escape.h 2009-06-19 16:48:48 UTC (rev 18135)
@@ -0,0 +1,45 @@
+/********************************************************************\
+ * escape.h : Escape SQL reserved characters in strings *
+ * Copyright (C) 2001 Linas Vepstas <linas at linas.org> *
+ * *
+ * This program is free software; you can redistribute it and/or *
+ * modify it under the terms of the GNU 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 General Public License*
+ * along with this program; if not, contact: *
+ * *
+ * Free Software Foundation Voice: +1-617-542-5942 *
+ * 51 Franklin Street, Fifth Floor Fax: +1-617-542-2652 *
+ * Boston, MA 02110-1301, USA gnu at gnu.org *
+\********************************************************************/
+
+/*
+ * FILE:
+ * escape.h
+ *
+ * FUNCTION:
+ * Escape SQL reserved characters \ and ' from strings
+ *
+ * HISTORY:
+ * Linas Vepstas January 2001
+ */
+
+#ifndef SQL_ESCAPE_H
+#define SQL_ESCAPE_H
+
+typedef struct _escape sqlEscape;
+
+sqlEscape * sqlEscape_new(void);
+void sqlEscape_destroy (sqlEscape *);
+
+const char * sqlEscapeString (sqlEscape *, const char *orig_string);
+
+#endif /* SQL_ESCAPE_H */
+
Modified: gnucash/trunk/src/backend/sql/gnc-backend-sql.c
===================================================================
--- gnucash/trunk/src/backend/sql/gnc-backend-sql.c 2009-06-15 22:19:35 UTC (rev 18134)
+++ gnucash/trunk/src/backend/sql/gnc-backend-sql.c 2009-06-19 16:48:48 UTC (rev 18135)
@@ -562,15 +562,8 @@
/* ---------------------------------------------------------------------- */
/* Query processing */
-#if 0
-static const gchar*
-convert_search_obj( QofIdType objType )
-{
- return (gchar*)objType;
-}
-
static void
-handle_and_term( QofQueryTerm* pTerm, gchar* sql )
+handle_and_term( QofQueryTerm* pTerm, GString* sql )
{
GSList* pParamPath;
QofQueryPredData* pPredData;
@@ -585,81 +578,100 @@
pPredData = qof_query_term_get_pred_data( pTerm );
isInverted = qof_query_term_is_inverted( pTerm );
- strcat( sql, "(" );
+ if( strcmp( pPredData->type_name, QOF_TYPE_GUID ) == 0 ) {
+ query_guid_t guid_data = (query_guid_t)pPredData;
+ GList* guid_entry;
+
+ for( name = pParamPath; name != NULL; name = name->next ) {
+ if( name != pParamPath ) g_string_append( sql, "." );
+ g_string_append( sql, name->data );
+ }
+
+ if( guid_data->options == QOF_GUID_MATCH_ANY ) {
+ if( isInverted ) g_string_append( sql, " NOT " );
+ g_string_append( sql, " IN (" );
+ }
+ for( guid_entry = guid_data->guids; guid_entry != NULL; guid_entry = guid_entry->next ) {
+ if( guid_entry != guid_data->guids ) g_string_append( sql, "." );
+ (void)guid_to_string_buff( guid_entry->data, val );
+ g_string_append( sql, "'" );
+ g_string_append( sql, val );
+ g_string_append( sql, "'" );
+ }
+ if( guid_data->options == QOF_GUID_MATCH_ANY ) {
+ g_string_append( sql, ")" );
+ }
+ }
+
+ g_string_append( sql, "(" );
if( isInverted ) {
- strcat( sql, "!" );
+ g_string_append( sql, "!" );
}
for( name = pParamPath; name != NULL; name = name->next ) {
- if( name != pParamPath ) strcat( sql, "." );
- strcat( sql, name->data );
+ if( name != pParamPath ) g_string_append( sql, "." );
+ g_string_append( sql, name->data );
}
if( pPredData->how == QOF_COMPARE_LT ) {
- strcat( sql, "<" );
+ g_string_append( sql, "<" );
} else if( pPredData->how == QOF_COMPARE_LTE ) {
- strcat( sql, "<=" );
+ g_string_append( sql, "<=" );
} else if( pPredData->how == QOF_COMPARE_EQUAL ) {
- strcat( sql, "=" );
+ g_string_append( sql, "=" );
} else if( pPredData->how == QOF_COMPARE_GT ) {
- strcat( sql, ">" );
+ g_string_append( sql, ">" );
} else if( pPredData->how == QOF_COMPARE_GTE ) {
- strcat( sql, ">=" );
+ g_string_append( sql, ">=" );
} else if( pPredData->how == QOF_COMPARE_NEQ ) {
- strcat( sql, "~=" );
+ g_string_append( sql, "~=" );
} else {
- strcat( sql, "??" );
+ g_string_append( sql, "??" );
}
if( strcmp( pPredData->type_name, "string" ) == 0 ) {
query_string_t pData = (query_string_t)pPredData;
- strcat( sql, "'" );
- strcat( sql, pData->matchstring );
- strcat( sql, "'" );
+ g_string_append( sql, "'" );
+ g_string_append( sql, pData->matchstring );
+ g_string_append( sql, "'" );
} else if( strcmp( pPredData->type_name, "date" ) == 0 ) {
query_date_t pData = (query_date_t)pPredData;
(void)gnc_timespec_to_iso8601_buff( pData->date, val );
- strcat( sql, "'" );
- strncat( sql, val, 4+1+2+1+2 );
- strcat( sql, "'" );
+ g_string_append( sql, "'" );
+ //g_string_append( sql, val, 4+1+2+1+2 );
+ g_string_append( sql, "'" );
} else if( strcmp( pPredData->type_name, "numeric" ) == 0 ) {
query_numeric_t pData = (query_numeric_t)pPredData;
- strcat( sql, "numeric" );
- } else if( strcmp( pPredData->type_name, "guid" ) == 0 ) {
- query_guid_t pData = (query_guid_t)pPredData;
- (void)guid_to_string_buff( pData->guids->data, val );
- strcat( sql, "'" );
- strcat( sql, val );
- strcat( sql, "'" );
+ g_string_append( sql, "numeric" );
+ } else if( strcmp( pPredData->type_name, QOF_TYPE_GUID ) == 0 ) {
} else if( strcmp( pPredData->type_name, "gint32" ) == 0 ) {
query_int32_t pData = (query_int32_t)pPredData;
sprintf( val, "%d", pData->val );
- strcat( sql, val );
+ g_string_append( sql, val );
} else if( strcmp( pPredData->type_name, "gint64" ) == 0 ) {
query_int64_t pData = (query_int64_t)pPredData;
sprintf( val, "%" G_GINT64_FORMAT, pData->val );
- strcat( sql, val );
+ g_string_append( sql, val );
} else if( strcmp( pPredData->type_name, "double" ) == 0 ) {
query_double_t pData = (query_double_t)pPredData;
sprintf( val, "%f", pData->val );
- strcat( sql, val );
+ g_string_append( sql, val );
} else if( strcmp( pPredData->type_name, "boolean" ) == 0 ) {
query_boolean_t pData = (query_boolean_t)pPredData;
sprintf( val, "%d", pData->val );
- strcat( sql, val );
+ g_string_append( sql, val );
} else {
g_assert( FALSE );
}
- strcat( sql, ")" );
+ g_string_append( sql, ")" );
}
-#endif
static void
compile_query_cb( const gchar* type, gpointer data_p, gpointer be_data_p )
@@ -682,6 +694,8 @@
}
}
+gchar* gnc_sql_compile_query_to_sql( GncSqlBackend* be, QofQuery* query );
+
/*@ null @*/ gpointer
gnc_sql_compile_query( QofBackend* pBEnd, QofQuery* pQuery )
{
@@ -695,6 +709,7 @@
ENTER( " " );
+//gnc_sql_compile_query_to_sql( be, pQuery );
searchObj = qof_query_get_search_for( pQuery );
pQueryInfo = g_malloc( (gsize)sizeof( gnc_sql_query_info ) );
@@ -714,40 +729,58 @@
return be_data.pQueryInfo;
}
-#if 0
- pBookList = qof_query_get_books( pQuery );
+ LEAVE( "" );
+ return pQueryInfo;
+}
+
+static const gchar*
+convert_search_obj( QofIdType objType )
+{
+ return (gchar*)objType;
+}
+
+gchar*
+gnc_sql_compile_query_to_sql( GncSqlBackend* be, QofQuery* query )
+{
+ QofIdType searchObj;
+ GList* bookList;
+ GString* sql;
+
+ g_return_val_if_fail( be != NULL, NULL );
+ g_return_val_if_fail( query != NULL, NULL );
+
+ searchObj = qof_query_get_search_for( query );
+ bookList = qof_query_get_books( query );
+
/* Convert search object type to table name */
- sprintf( sql, "SELECT * from %s", convert_search_obj( searchObj ) );
- if( !qof_query_has_terms( pQuery ) ) {
- strcat( sql, ";" );
+ sql = g_string_new( "" );
+ g_string_append( sql, "SELECT * FROM " );
+ g_string_append( sql, convert_search_obj( searchObj ) );
+ if( !qof_query_has_terms( query ) ) {
+ g_string_append( sql, ";" );
} else {
- GList* pOrTerms = qof_query_get_terms( pQuery );
+ GList* orterms = qof_query_get_terms( query );
GList* orTerm;
- strcat( sql, " WHERE " );
+ g_string_append( sql, " WHERE " );
- for( orTerm = pOrTerms; orTerm != NULL; orTerm = orTerm->next ) {
- GList* pAndTerms = (GList*)orTerm->data;
+ for( orTerm = orterms; orTerm != NULL; orTerm = orTerm->next ) {
+ GList* andterms = (GList*)orTerm->data;
GList* andTerm;
- if( orTerm != pOrTerms ) strcat( sql, " OR " );
- strcat( sql, "(" );
- for( andTerm = pAndTerms; andTerm != NULL; andTerm = andTerm->next ) {
- if( andTerm != pAndTerms ) strcat( sql, " AND " );
+ if( orTerm != orterms ) g_string_append( sql, " OR " );
+ g_string_append( sql, "(" );
+ for( andTerm = andterms; andTerm != NULL; andTerm = andTerm->next ) {
+ if( andTerm != andterms ) g_string_append( sql, " AND " );
handle_and_term( (QofQueryTerm*)andTerm->data, sql );
}
- strcat( sql, ")" );
+ g_string_append( sql, ")" );
}
}
- DEBUG( "Compiled: %s\n", sql );
- pQueryInfo->pCompiledQuery = g_strdup( sql );
-#endif
-
- LEAVE( "" );
-
- return pQueryInfo;
+ DEBUG( "Compiled: %s\n", sql->str );
+ return g_string_free( sql, FALSE );
}
static void
@@ -1520,6 +1553,25 @@
#define TIMESPEC_STR_FORMAT "%04d%02d%02d%02d%02d%02d"
#define TIMESPEC_COL_SIZE (4+2+2+2+2+2)
+gchar*
+gnc_sql_convert_timespec_to_string( Timespec ts )
+{
+ time_t time;
+ struct tm* tm;
+ gint year;
+ gchar* datebuf;
+
+ time = timespecToTime_t( ts );
+ tm = gmtime( &time );
+
+ if( tm->tm_year < 60 ) year = tm->tm_year + 2000;
+ else year = tm->tm_year + 1900;
+
+ datebuf = g_strdup_printf( TIMESPEC_STR_FORMAT,
+ year, tm->tm_mon+1, tm->tm_mday, tm->tm_hour, tm->tm_min, tm->tm_sec );
+ return datebuf;
+}
+
static void
load_timespec( const GncSqlBackend* be, GncSqlRow* row,
/*@ null @*/ QofSetterFunc setter, gpointer pObject,
@@ -1582,9 +1634,6 @@
TimespecAccessFunc ts_getter;
Timespec ts;
gchar* datebuf;
- time_t time;
- struct tm* tm;
- gint year;
GValue* value;
g_return_if_fail( be != NULL );
@@ -1597,14 +1646,7 @@
g_return_if_fail( ts_getter != NULL );
ts = (*ts_getter)( pObject );
- time = timespecToTime_t( ts );
- tm = gmtime( &time );
-
- if( tm->tm_year < 60 ) year = tm->tm_year + 2000;
- else year = tm->tm_year + 1900;
-
- datebuf = g_strdup_printf( TIMESPEC_STR_FORMAT,
- year, tm->tm_mon+1, tm->tm_mday, tm->tm_hour, tm->tm_min, tm->tm_sec );
+ datebuf = gnc_sql_convert_timespec_to_string( ts );
value = g_new0( GValue, 1 );
g_assert( value != NULL );
(void)g_value_init( value, G_TYPE_STRING );
Modified: gnucash/trunk/src/backend/sql/gnc-backend-sql.h
===================================================================
--- gnucash/trunk/src/backend/sql/gnc-backend-sql.h 2009-06-15 22:19:35 UTC (rev 18134)
+++ gnucash/trunk/src/backend/sql/gnc-backend-sql.h 2009-06-19 16:48:48 UTC (rev 18135)
@@ -679,6 +679,14 @@
gint64 gnc_sql_get_integer_value( const GValue* value );
/**
+ * Converts a Timespec value to a string value for the database.
+ *
+ * @param ts Timespec to be converted
+ * @return String representation of the Timespec
+ */
+gchar* gnc_sql_convert_timespec_to_string( Timespec ts );
+
+/**
* Upgrades a table to a new structure. The upgrade is done by creating a new table with
* the new structure, SELECTing the old data into the new table, deleting the old table,
* then renaming the new table. Therefore, this will only work if the new table structure
Modified: gnucash/trunk/src/backend/sql/gnc-transaction-sql.c
===================================================================
--- gnucash/trunk/src/backend/sql/gnc-transaction-sql.c 2009-06-15 22:19:35 UTC (rev 18134)
+++ gnucash/trunk/src/backend/sql/gnc-transaction-sql.c 2009-06-19 16:48:48 UTC (rev 18135)
@@ -47,6 +47,8 @@
#include "gnc-engine.h"
+#include "escape.h"
+
#ifdef S_SPLINT_S
#include "splint-defs.h"
#endif
@@ -722,42 +724,316 @@
}
}
+static void
+convert_query_comparison_to_sql( QofQueryPredData* pPredData, gboolean isInverted, GString* sql )
+{
+ if( pPredData->how == QOF_COMPARE_LT
+ || ( isInverted && pPredData->how == QOF_COMPARE_GTE ) ) {
+ g_string_append( sql, "<" );
+ } else if( pPredData->how == QOF_COMPARE_LTE
+ || ( isInverted && pPredData->how == QOF_COMPARE_GT ) ) {
+ g_string_append( sql, "<=" );
+ } else if( pPredData->how == QOF_COMPARE_EQUAL
+ || ( isInverted && pPredData->how == QOF_COMPARE_NEQ ) ) {
+ g_string_append( sql, "=" );
+ } else if( pPredData->how == QOF_COMPARE_GT
+ || ( isInverted && pPredData->how == QOF_COMPARE_LTE ) ) {
+ g_string_append( sql, ">" );
+ } else if( pPredData->how == QOF_COMPARE_GTE
+ || ( isInverted && pPredData->how == QOF_COMPARE_LT ) ) {
+ g_string_append( sql, ">=" );
+ } else if( pPredData->how == QOF_COMPARE_NEQ
+ || ( isInverted && pPredData->how == QOF_COMPARE_EQUAL ) ) {
+ g_string_append( sql, "~=" );
+ } else {
+ PERR( "Unknown comparison type\n" );
+ g_string_append( sql, "??" );
+ }
+}
+
+static void
+convert_query_term_to_sql( const gchar* fieldName, QofQueryTerm* pTerm, GString* sql )
+{
+ GSList* pParamPath;
+ QofQueryPredData* pPredData;
+ gboolean isInverted;
+ GSList* name;
+ gchar val[GUID_ENCODING_LENGTH+1];
+
+ g_return_if_fail( pTerm != NULL );
+ g_return_if_fail( sql != NULL );
+
+ pParamPath = qof_query_term_get_param_path( pTerm );
+ pPredData = qof_query_term_get_pred_data( pTerm );
+ isInverted = qof_query_term_is_inverted( pTerm );
+
+ if( safe_strcmp( pPredData->type_name, QOF_TYPE_GUID ) == 0 ) {
+ query_guid_t guid_data = (query_guid_t)pPredData;
+ GList* guid_entry;
+
+ g_string_append( sql, "(" );
+ g_string_append( sql, fieldName );
+
+ switch( guid_data->options ) {
+ case QOF_GUID_MATCH_ANY:
+ if( isInverted ) g_string_append( sql, " NOT IN (" );
+ else g_string_append( sql, " IN (" );
+ break;
+
+ case QOF_GUID_MATCH_NONE:
+ if( isInverted ) g_string_append( sql, " IN (" );
+ else g_string_append( sql, " NOT IN (" );
+ break;
+
+ default:
+ PERR( "Unexpected GUID match type: %d\n", guid_data->options );
+ }
+
+ for( guid_entry = guid_data->guids; guid_entry != NULL; guid_entry = guid_entry->next ) {
+ if( guid_entry != guid_data->guids ) g_string_append( sql, "," );
+ (void)guid_to_string_buff( guid_entry->data, val );
+ g_string_append( sql, "'" );
+ g_string_append( sql, val );
+ g_string_append( sql, "'" );
+ break;
+ }
+ g_string_append( sql, ")" );
+ g_string_append( sql, ")" );
+ return;
+
+ } else if( safe_strcmp( pPredData->type_name, QOF_TYPE_CHAR ) == 0 ) {
+ query_char_t char_data = (query_char_t)pPredData;
+ int i;
+
+ if( isInverted ) {
+ g_string_append( sql, "NOT(" );
+ }
+ if( char_data->options == QOF_CHAR_MATCH_NONE ) {
+ g_string_append( sql, "NOT " );
+ }
+ g_string_append( sql, "(" );
+ for( i = 0; char_data->char_list[i] != '\0'; i++ ) {
+ if( i != 0 ) {
+ g_string_append( sql, " OR " );
+ }
+ g_string_append( sql, fieldName );
+ g_string_append( sql, " = '" );
+ g_string_append_c( sql, char_data->char_list[i] );
+ g_string_append( sql, "'" );
+ }
+ g_string_append( sql, ") " );
+ if( isInverted ) {
+ g_string_append( sql, ") " );
+ }
+ return;
+
+ } else if( safe_strcmp( pPredData->type_name, QOF_TYPE_DATE ) == 0 ) {
+ query_date_t date_data = (query_date_t)pPredData;
+ gchar* datebuf;
+
+ g_string_append( sql, "(" );
+ g_string_append( sql, fieldName );
+ convert_query_comparison_to_sql( pPredData, isInverted, sql );
+ datebuf = gnc_sql_convert_timespec_to_string( date_data->date );
+ g_string_append( sql, "'" );
+ g_string_append( sql, datebuf );
+ g_string_append( sql, "')" );
+ return;
+
+ } else if( safe_strcmp( pPredData->type_name, QOF_TYPE_STRING ) == 0 ) {
+ query_string_t string_data = (query_string_t)pPredData;
+ sqlEscape* escape = sqlEscape_new();
+
+ if( isInverted ) {
+ g_string_append( sql, "NOT(" );
+ }
+ if( pPredData->how == QOF_COMPARE_NEQ ) {
+ g_string_append( sql, "NOT(" );
+ }
+ g_string_append( sql, fieldName );
+ if( string_data->is_regex || string_data->options == QOF_STRING_MATCH_CASEINSENSITIVE ) {
+ PWARN( "String is_regex || option = QOF_STRING_MATCH_INSENSITIVE\n" );
+ }
+// g_string_append( sql, " ~" );
+// } else {
+ g_string_append( sql, " =" );
+// }
+// if( string_data->options == QOF_STRING_MATCH_CASEINSENSITIVE ) {
+// g_string_append( sql, "*" );
+// }
+ g_string_append( sql, "'" );
+ g_string_append( sql, sqlEscapeString( escape, string_data->matchstring ) );
+ g_string_append( sql, "'" );
+ if( pPredData->how == QOF_COMPARE_NEQ ) {
+ g_string_append( sql, ")" );
+ }
+ if( isInverted ) {
+ g_string_append( sql, ")" );
+ }
+ sqlEscape_destroy( escape );
+ return;
+ }
+
+ g_string_append( sql, "(" );
+ g_string_append( sql, fieldName );
+ convert_query_comparison_to_sql( pPredData, isInverted, sql );
+
+ if( strcmp( pPredData->type_name, "numeric" ) == 0 ) {
+ query_numeric_t pData = (query_numeric_t)pPredData;
+
+ g_string_append( sql, "numeric" );
+ } else if( strcmp( pPredData->type_name, "gint32" ) == 0 ) {
+ query_int32_t pData = (query_int32_t)pPredData;
+
+ sprintf( val, "%d", pData->val );
+ g_string_append( sql, val );
+ } else if( strcmp( pPredData->type_name, "gint64" ) == 0 ) {
+ query_int64_t pData = (query_int64_t)pPredData;
+
+ sprintf( val, "%" G_GINT64_FORMAT, pData->val );
+ g_string_append( sql, val );
+ } else if( strcmp( pPredData->type_name, "double" ) == 0 ) {
+ query_double_t pData = (query_double_t)pPredData;
+
+ sprintf( val, "%f", pData->val );
+ g_string_append( sql, val );
+ } else if( strcmp( pPredData->type_name, "boolean" ) == 0 ) {
+ query_boolean_t pData = (query_boolean_t)pPredData;
+
+ sprintf( val, "%d", pData->val );
+ g_string_append( sql, val );
+ } else {
+ PERR( "Unknown query predicate type: %s\n", pPredData->type_name );
+ }
+
+ g_string_append( sql, ")" );
+}
+
typedef struct {
GncSqlStatement* stmt;
- Account* acct;
gboolean has_been_run;
} split_query_info_t;
static /*@ null @*/ gpointer
-compile_split_query( GncSqlBackend* be, QofQuery* pQuery )
+compile_split_query( GncSqlBackend* be, QofQuery* query )
{
const GUID* acct_guid;
gchar guid_buf[GUID_ENCODING_LENGTH+1];
split_query_info_t* query_info = NULL;
gchar* subquery_sql;
gchar* query_sql;
+ GString* subquery_tables;
+ gboolean has_transactions_table = FALSE;
g_return_val_if_fail( be != NULL, NULL );
- g_return_val_if_fail( pQuery != NULL, NULL );
+ g_return_val_if_fail( query != NULL, NULL );
- acct_guid = get_guid_from_query( pQuery );
- if( acct_guid != NULL ) {
- (void)guid_to_string_buff( acct_guid, guid_buf );
- subquery_sql = g_strdup_printf( "SELECT DISTINCT tx_guid FROM %s WHERE account_guid='%s'", SPLIT_TABLE, guid_buf );
- query_sql = g_strdup_printf( "SELECT * FROM %s WHERE guid IN (%s)", TRANSACTION_TABLE, subquery_sql );
+ query_info = g_malloc( (gsize)sizeof(split_query_info_t) );
+ g_assert( query_info != NULL );
+ query_info->has_been_run = FALSE;
- query_info = g_malloc( (gsize)sizeof(split_query_info_t) );
- g_assert( query_info != NULL );
+ subquery_tables = g_string_new( SPLIT_TABLE );
+ if( qof_query_has_terms( query ) ) {
+ GList* orterms = qof_query_get_terms( query );
+ GList* orTerm;
+ GString* sql = g_string_new( "" );
+ gboolean need_OR = FALSE;
+
+ for( orTerm = orterms; orTerm != NULL; orTerm = orTerm->next ) {
+ GList* andterms = (GList*)orTerm->data;
+ GList* andTerm;
+ gboolean need_AND = FALSE;
+ gboolean has_tx_guid_check = FALSE;
+
+ if( need_OR ) {
+ g_string_append( sql, " OR " );
+ }
+ g_string_append( sql, "(" );
+ for( andTerm = andterms; andTerm != NULL; andTerm = andTerm->next ) {
+ QofQueryTerm* term;
+ GSList* paramPath;
+ gboolean unknownPath = FALSE;
+
+ term = (QofQueryTerm*)andTerm->data;
+ paramPath = qof_query_term_get_param_path( term );
+
+ if( strcmp( paramPath->data, QOF_PARAM_BOOK ) == 0 ) continue;
+
+ if( need_AND ) g_string_append( sql, " AND " );
+
+ if( strcmp( paramPath->data, SPLIT_ACCOUNT ) == 0
+ && strcmp( paramPath->next->data, QOF_PARAM_GUID ) == 0 ) {
+ convert_query_term_to_sql( "account_guid", term, sql );
+
+ } else if( strcmp( paramPath->data, SPLIT_RECONCILE ) == 0 ) {
+ convert_query_term_to_sql( "reconcile_state", term, sql );
+
+ } else if( strcmp( paramPath->data, SPLIT_TRANS ) == 0 ) {
+ if( !has_transactions_table ) {
+ g_string_append( subquery_tables, ", " );
+ g_string_append( subquery_tables, TRANSACTION_TABLE );
+ has_transactions_table = TRUE;
+ }
+ if( !has_tx_guid_check ) {
+ g_string_append( sql, "(splits.tx_guid = transactions.guid) AND " );
+ has_tx_guid_check = TRUE;
+ }
+ if( strcmp( paramPath->next->data, TRANS_DATE_POSTED ) == 0 ) {
+ convert_query_term_to_sql( "transactions.post_date", term, sql );
+ } else if( strcmp( paramPath->next->data, TRANS_DESCRIPTION ) == 0 ) {
+ convert_query_term_to_sql( "transactions.description", term, sql );
+ } else {
+ unknownPath = TRUE;
+ }
+
+ } else {
+ unknownPath = TRUE;
+ }
+
+ if( unknownPath ) {
+ GString* name = g_string_new( (gchar*)paramPath->data );
+ while( paramPath->next != NULL ) {
+ g_string_append( name, "." );
+ g_string_append( name, paramPath->next->data );
+ paramPath = paramPath->next;
+ }
+ PERR( "Unknown SPLIT query field: %s\n", name->str );
+ g_string_free( name, TRUE );
+ }
+ need_AND = TRUE;
+ }
+
+ /* If the last char in the string is a '(', then for some reason, there were
+ no terms added to the SQL. If so, remove it and ignore the OR term. */
+ if( sql->str[sql->len-1] == '(' ) {
+ g_string_truncate( sql, sql->len-1 );
+ need_OR = FALSE;
+ } else {
+ g_string_append( sql, ")" );
+ need_OR = TRUE;
+ }
+ }
+
+ if( sql->len != 0 ) {
+ subquery_sql = g_strdup_printf( "SELECT DISTINCT tx_guid FROM %s WHERE %s",
+ g_string_free( subquery_tables, FALSE ),
+ sql->str );
+ query_sql = g_strdup_printf( "SELECT * FROM %s WHERE guid IN (%s)",
+ TRANSACTION_TABLE, subquery_sql );
+ g_free( subquery_sql );
+ } else {
+ query_sql = g_strdup_printf( "SELECT * FROM %s", TRANSACTION_TABLE );
+ }
query_info->stmt = gnc_sql_create_statement_from_sql( be, query_sql );
+
+ g_string_free( sql, TRUE );
g_free( query_sql );
- query_info->has_been_run = FALSE;
- query_info->acct = xaccAccountLookup( acct_guid, be->primary_book );
- if( query_info->acct == NULL ) {
- PWARN( "Unable to find account with guid='%s'\n", guid_buf );
- }
- g_free( subquery_sql );
+ } else {
+ query_sql = g_strdup_printf( "SELECT * FROM %s", TRANSACTION_TABLE );
+ query_info->stmt = gnc_sql_create_statement_from_sql( be, query_sql );
+ g_free( query_sql );
}
return query_info;
@@ -770,16 +1046,12 @@
g_return_if_fail( be != NULL );
g_return_if_fail( pQuery != NULL );
- g_return_if_fail( query_info->acct != NULL );
- // When the query to load all splits for the account has been run, set the
- // mark so that this account's query is not reexecuted.
- if( !query_info->has_been_run && xaccAccountGetMark( query_info->acct ) == 0 ) {
+ if( !query_info->has_been_run ) {
query_transactions( be, query_info->stmt );
query_info->has_been_run = TRUE;
gnc_sql_statement_dispose( query_info->stmt );
query_info->stmt = NULL;
- xaccAccountSetMark( query_info->acct, 1 );
}
}
More information about the gnucash-changes
mailing list