dbelement.c

linux下的电话本的最底层

		
			g_string_assign(sql_str, "");
			g_string_append_printf(sql_str,
														 "SELECT * FROM %s_ext_%s",
														 element->db_elm_name,
														 ftstrs[i]);
			if (uids_str)
			{
				g_string_append_printf(sql_str,
															 " WHERE ftid%d IN ( %s );",
															 element->db_elm_id_ftid,
															 valid_uids_str->str);
			}
		
/*			g_print("%s(): sql_str = %s\n",
							__FUNCTION__,
							sql_str->str);
*/							
			do
			{
				ret = sqlite3_prepare(element->db->database,
															sql_str->str,
															-1,
															&pStmt,
															0);
				if (ret != SQLITE_OK)
				{
						g_print("%s(): sqlite3_step() WARNING: %d[%s].\n",
										__FUNCTION__,
										sqlite3_errcode(element->db->database),
										sqlite3_errmsg(element->db->database));
				
						goto failure;
				}
		
				while ((ret = sqlite3_step(pStmt)) == SQLITE_ROW)
				{
					guint32 uid = 0;
					guint32 fid = FIELD_ID_INVALID;
					const gchar * label = NULL;
					const gchar * desc = NULL;
					Record * record = NULL;
					
					uid = sqlite3_column_int(pStmt, 0);
					fid = sqlite3_column_int(pStmt, 1);
					label = sqlite3_column_text(pStmt, 2);
					desc = sqlite3_column_text(pStmt, 3);
					
					record = g_hash_table_lookup(results,
																			 &uid);
					
					if (record)
					{
						record_add_know_field(record, fid);
						
						if (label)
						{
							record_set_label(record,
															 fid,
															 label);
						}
		
						if (desc)
						{
							record_set_desc(record,
															fid,
															label);
						}
		
						if (sqlite3_column_type(pStmt, 4) == SQLITE_BLOB)
						{
							record_set_field(record,
															 fid,
															 sqlite3_column_blob(pStmt, 4),
															 sqlite3_column_bytes(pStmt, 4));
						}
						else
						{
							gpointer value = NULL;
					
							value = dump_field_value(pStmt, 4);
							record_set_field(record,
															 fid,
															 value,
															 FIELD_SIZE_AUTO);
							g_free(value);
						}
					}
//					record_print(record);
				}
				
				if (ret == SQLITE_BUSY)
				{
					sqlite3_finalize(pStmt);
					
					goto failure;
				}
				
				ret = sqlite3_finalize(pStmt);
			} while (ret == SQLITE_SCHEMA);
		}
	}
	
	record_db_inner_trans_commit(element->db);
	
	*iter = iterator_new_full((IterFreeFunc)record_free);
	g_hash_table_foreach(results,
											 iter_parse_record_from_hash_table,
											 (gpointer)*iter);
	g_hash_table_destroy(results);
	
	if (uids_str)
	{
		g_string_free(uids_str, TRUE);
	}
	
	if (valid_uids_str)
	{
		g_string_free(valid_uids_str, TRUE);
	}
	
	g_string_free(sql_str, TRUE);

	return DB_ERROR_NONE;
	
failure:
	*iter = NULL;
	g_hash_table_destroy(results);
	
	if (uids_str)
	{
		g_string_free(uids_str, TRUE);
	}
	
	if (valid_uids_str)
	{
		g_string_free(valid_uids_str, TRUE);
	}

	g_string_free(sql_str, TRUE);

	if (ret == SQLITE_BUSY)
	{
		return DB_ERROR_BUSY;
	}
	
	if (*iter == NULL)
	{
		return DB_ERROR_RESULT_NULL;
	}

	return DB_ERROR_FAILED;
}

/**
 * @brief		Retrieve count of #Record objects from 
 * 					#RecordDBElement database which match the conditions
 * 					described in sample #Record objects.
 *
 * @param		element			the #RecordDBElement object
 * @param		sample			the sample #Record object describes the 
 * 											search conditions
 * @param		fuzzy				does a pattern matching comparison in searching
 * 											if this is set TRUE. A percent symbol '%' in the 
 * 											pattern matches any	sequence of zero or more 
 * 											characters in the string. An underscore '_' in 
 * 											the pattern matches any single character in the
 * 											string. The pattern	will only applies on STRING	
 * 											type fields.   
 * @param		intersect		all the #Record objects in results set MUST match 
 * 											every condition described in sample #Record object if
 * 											this is set TRUE. Otherwise, the #Record obecjts only
 * 											need match one condition at lease.
 * 
 * @return	DB_ERROR_NONE if operation was successfully.
 * 
 * @note		the retrieved #Iteraotr object of results set should be freed
 * 					when no longer needed.
 */
RecordDBError
record_db_element_get_records_count (RecordDBElement				*element,
																		 const Record						*sample,
																		 gboolean								 fuzzy,
																		 gboolean								 intersect,
																		 guint									*count)
{
	sqlite3_stmt * pStmt = NULL;
	GString * sql_str = NULL;
	GString * uids_str = NULL;
	guint8 field_types = 0x0;
	GHashTable * results = NULL;
	gint i;
	gint ret;

	g_return_val_if_fail(element, DB_ERROR_ARG_NULL);
	g_return_val_if_fail(count, DB_ERROR_ARG_NULL);

	*count = 0;
	results = g_hash_table_new_full(g_int_hash,
																	g_int_equal,
																	g_free,
																	NULL);
	sql_str = g_string_new("");
	
	if (sample)
	{
		GString * cond_str = NULL;
		Iterator * iter_ftids = NULL;
		gchar * compound_op = NULL;
		gssize pos;
		
		if (record_is_extended(sample))
		{
			g_print("%s(): WARN: extend fields in sample record will be IGNORED.\n",
							__FUNCTION__);
		}
		
		compound_op = intersect ? " INTERSECT " : " UNION ";

		cond_str = g_string_new("");
													 
		iter_ftids = record_get_ftids(sample);
		for (iterator_to_first(iter_ftids);
				 !iterator_at_last(iter_ftids);
				 iterator_next(iter_ftids))
		{
			const FieldDescriptor * fdesc = NULL; 
			guint32 ftid = *(guint32 *)iterator_current(iter_ftids);
			
			if (ftid == element->db_elm_id_ftid)
			{
				continue;
			}
			
			if (record_get_field_default(sample,
																	 ftid,
																	 NULL) == NULL)
			{
				continue;
			}
			
			fdesc = field_template_get(sample->template, ftid);
			if (fdesc)
			{
				DBFieldType type = DB_FIELDS_TEXT;
				
				switch (fdesc->type)
				{
					case FIELD_TYPE_BOOLEAN:
					case FIELD_TYPE_DATE:
					case FIELD_TYPE_INTEGER:
						type = DB_FIELDS_INTEGER;
						break;
						
					case FIELD_TYPE_FLOAT:
						type = DB_FIELDS_REAL;
						break;
						
					case FIELD_TYPE_BINARY:
						type = DB_FIELDS_BLOB;
						break;
						
					case FIELD_TYPE_STRING:
					default:
						type = DB_FIELDS_TEXT;
						break;
				}

				g_string_append_printf(cond_str,
															 "SELECT ftid%d FROM ( ",
															 element->db_elm_id_ftid);
				g_string_append_printf(cond_str,
															 "SELECT ftid%d FROM %s_main WHERE ( ftid%d %s :%03d )",
															 element->db_elm_id_ftid,
															 element->db_elm_name,
															 ftid,
															 (((type == DB_FIELDS_TEXT) && fuzzy)? "LIKE" : "="),
															 ftid);

				g_string_append_printf(cond_str,
															 " UNION SELECT ftid%d FROM %s_ext_%s WHERE (( value %s :%03d ) & (fid / %d = %d))",
															 element->db_elm_id_ftid,
															 element->db_elm_name,
															 ftstrs[type],
															 (((type == DB_FIELDS_TEXT) && fuzzy)? "LIKE" : "="),
															 ftid,
															 FID_GENERATE_SEED,
															 ftid);
				g_string_append_printf(cond_str,
															 ")%s",
															 compound_op);
			}
		}
		
		pos = g_strrstr_len(cond_str->str, cond_str->len, compound_op) - cond_str->str;
		g_string_erase(cond_str, pos, -1);
		g_string_append(cond_str, ";");
		
/*		g_print("%s(): cond_str = %s\n",
						__FUNCTION__,
						cond_str->str);
*/	
		uids_str = g_string_new("");																

		record_db_inner_trans_begin(element->db,
																DB_TRANS_DEFERRED);
		do
		{
			ret = sqlite3_prepare(element->db->database,
														cond_str->str,
														-1,
														&pStmt,
														0);
			if (ret != SQLITE_OK)
			{
					g_print("%s(): sqlite3_step() WARNING: %d[%s].\n",
									__FUNCTION__,
									sqlite3_errcode(element->db->database),
									sqlite3_errmsg(element->db->database));
				
					iterator_free(iter_ftids, TRUE);
					goto failure;
			}
			
			for (iterator_to_first(iter_ftids); 
					 !iterator_at_last(iter_ftids);
					 iterator_next(iter_ftids))
			{
				const FieldDescriptor * fdesc = NULL;
				guint32 ftid = *(guint32 *)iterator_current(iter_ftids);
				
				fdesc = field_template_get(element->db_elm_template,
																	 ftid);
				if (fdesc)
				{
					gconstpointer value = NULL;
					FieldSize size = 0;
					
					value = record_get_field_default(sample,
																					 ftid,
																					 &size);
					if (value == NULL)
					{
						continue;
					}
/*					g_print("%s(): ftid = %3d, value = 0x%08x, size = %d\n",
									__FUNCTION__,
									fdesc->identifier,
									(guint32)value, 
									size);
*/	
					bind_value_by_fid(pStmt,
														fdesc->identifier,
														value,
														fdesc->type,
														size);
					bind_value_by_fid_2(pStmt,
															fdesc->identifier,
															value,
															fdesc->type,
															size);
				}
			}
			
			while ((ret = sqlite3_step(pStmt)) == SQLITE_ROW)
			{
				gint count;
				guint32 uid = 0;
				
				uid = sqlite3_column_int(pStmt, 0);
	/*			g_print("%s(): uid = %d\n",
								__FUNCTION__,
								uid);
	*/
				if (g_hash_table_lookup(results, &uid) == NULL)
				{
					guint32 * key = NULL;
					
					key = g_new0(guint32, 1);
					*key = uid;
					
					g_hash_table_replace(results,
															 key,
															 (gpointer)record_new(element->db_elm_template));
				}
				
				g_string_append_printf(uids_str,
															 "%d, ",
															 uid);
			}
					
			if (ret == SQLITE_BUSY)
			{
				sqlite3_finalize(pStmt);
					
				iterator_free(iter_ftids, TRUE);
				goto failure;
			}
				
			ret = sqlite3_finalize(pStmt);
		} while (ret == SQLITE_SCHEMA);
	
		/*
		 * No match records found
		 */
		if (uids_str->len == 0)
		{
			goto failure;
		}
		
		pos = g_strrstr_len(uids_str->str, uids_str->len, ", ") - uids_str->str;
		g_string_erase(uids_str, pos, -1);
		
		iterator_free(iter_ftids, TRUE);
	}
	
	g_string_assign(sql_str, "");
	g_string_append_printf(sql_str, 
												 "SELECT count(*) FROM %s_main",
												 element->db_elm_name);
	if (uids_str)
	{
		g_string_append_printf(sql_str,
													 " WHERE ftid%d IN ( %s );",
													 element->db_elm_id_ftid,
													 uids_str->str);
	}
/*	g_print("%s(): sql_str = %s\n",
					__FUNCTION__,
					sql_str->str);
*/
	do
	{
		ret = sqlite3_prepare(element->db->database,
													sql_str->str,
													-1,
													&pStmt,
													0);
		if (ret != SQLITE_OK)
		{
			g_print("%s(): sqlite3_step() WARNING: %d[%s].\n",
							__FUNCTION__,
							sqlite3_errcode(element->db->database),
							sqlite3_errmsg(element->db->database));
				
			goto failure;
		}
		
		while ((ret = sqlite3_step(pStmt)) == SQLITE_ROW)
		{
/*			g_print("%s(): count = %d\n",
							__FUNCTION__,
							count);
*/			
			*count = sqlite3_column_int(pStmt, 0);
		}
				
		if (ret == SQLITE_BUSY)
		{
			sqlite3_finalize(pStmt);
				
			goto failure;
		}
			
		ret = sqlite3_finalize(pStmt);
	} while (ret == SQLITE_SCHEMA);

	if (uids_str)
	{
		g_string_free(uids_str, TRUE);
	}
	g_string_free(sql_str, TRUE);

	return (*count ? DB_ERROR_RESULT_NULL : DB_ERROR_NONE);
	
failure:
	g_hash_table_destroy(results);
	
	if (uids_str)
	{
		g_string_free(uids_str, TRUE);
	}
	g_string_free(sql_str, TRUE);

	if (ret == SQLITE_BUSY)
	{
		return DB_ERROR_BUSY;
	}
	
	if (*count == 0)
	{
		return DB_ERROR_RESULT_NULL;
	}

	return DB_ERROR_FAILED;
}

/**
 * @brief		Update a #Record object #RecordDBElement database which match the conditions
 * 					described in sample #Record objects.
 *
 * @param		element			the #RecordDBElement object
 * @param		record			the #Iterator object holds the results set
 * 
 * @return	DB_ERROR_NONE if operation was successfully.
 * 
 * @note		the value of primary field of #Record object
 * 					must be set. 					
 */
RecordDBError
record_db_element_update_record (RecordDBElement	*element,
																 Record						*record)
{
	Iterator * iter = NULL;
	GString * sql_str = NULL;
	sqlite3_stmt * pStmt = NULL;
	sqlite3_stmt * pStmtExts[DB_FIELDS_NTYPES];
	const FieldDescriptor * prifdesc = NULL;
	gconstpointer id = 0;
	FieldSize prisize = 0;
	gint i;
	gint ret;
	
	g_return_val_if_fail(element, DB_ERROR_ARG_NULL);
	g_return_val_if_fail(record, DB_ERROR_ARG_NULL);
	
	prifdesc = field_template_get(record->template, 
																element->db_elm_id_ftid);
	id = record_get_field_default(record,
																			element->db_elm_id_ftid,
																			&prisize);
	if (id == NULL)
	{
		g_print("%s(): set primary field firstly.\n",
						__FUNCTION__);
		return DB_ERROR_FAILED;
	}
	
	memset(pStmtExts, 0, sizeof(pStmtExts));

	/*
	 * insert data for main table
	 */
	sql_str = g_string_new