Grid Application Toolkit A simple API for Grid Applications

Menu Home API-Specification API-Overview Status C Reference Manual Download Java Java-Reference General GridLab - WP-1 Links

advertservice_database.c Go to the documentation of this file. /** @file advertservice_database.c * Source file for the database routines used to simulate a real advert * service. * * @date Fri Mar 19 2004 * * @version $Header: /export/cvs-gridlab/wp-1/Codes/GATEngine/C-reference/adaptors/advertservice_adaptor/advertservice_database.c,v 1.9 2004/04/18 15:04:59 merzky Exp $ * * Copyright (C) Hartmut Kaiser * This file is part of the GAT Engine. * Contributed by Hartmut Kaiser <hartmutkaiser [at] t-online [dot] de>. * * Use, modification and distribution is subject to the Gridlab Software * License. (See accompanying file GLlicense.txt or copy at * http://www.gridlab.org/GLlicense.txt) */ static const char *rcsid = "$Header: /export/cvs-gridlab/wp-1/Codes/GATEngine/C-reference/adaptors/advertservice_adaptor/advertservice_database.c,v 1.9 2004/04/18 15:04:59 merzky Exp $"; /* System Header Files */ #include <stdio.h> #include <stdlib.h> #include <string.h> #include <GAT.h> #include <GATUtil.h> #include "regex.h" #include "advertservice_database.h" /* Macros */ #if !defined(MAX_DB_NAME) #define MAX_DB_NAME 256 #endif // !defined(MAX_DB_NAME) /* Structures, unions and enums */ /* Static function prototypes */ static GATResult advertservice_db_get_version(sqlite *db, GATuint32 *version, char **err_msg); static GATResult advertservice_db_execute_sql_statements(sqlite *db, char const **stmts, char **err_msg); static GATResult advertservice_db_begin_transaction(sqlite *db, char **err_msg); static GATResult advertservice_db_commit_transaction(sqlite *db, char **err_msg); static GATResult advertservice_db_find_node(sqlite *db, char const *path, int *node_id, GATBool createnode, char **err_msg); static GATResult advertservice_db_write_data(sqlite *db, int node_id, char const *data, GATTable_const metadata, char **err_msg); static GATResult advertservice_db_delete_node(sqlite *db, int node_id, char **err_msg); static GATResult advertservice_db_get_node_metadata(sqlite *db, int node_id, GATTable *metadata, char **err_msg); static GATResult advertservice_db_get_node_data(sqlite *db, int node_id, char **advert_data, char **err_msg); static GATResult advertservice_db_find_pathes(sqlite *db, int nrows, char **values, GATList_String node_paths, char **err_msg); static GATResult advertservice_db_construct_path(sqlite *db, char *node_id_str, char **path, char **err_msg); static int advertservice_db_read_node_id(sqlite *db, char const *node_name, int parent_id, int *node_id, char **err_msg); static int advertservice_db_alter_table(sqlite *db, char const **keys, char **err_msg); static int advertservice_db_create_datatable(sqlite *db, char **old_fields, int count, char **new_fields, int table_number, char **err_msg); static int advertservice_db_copy_datatable(sqlite *db, char **old_fields, int count, int table_number, char **err_msg); static int advertservice_db_add_metadata(sqlite *db, char **new_fields, char **err_msg); static int advertservice_db_get_datatable_number(sqlite *db, int *number, char **err_msg); static void advertservice_db_re_match(sqlite_func *func, int argc, const char **argv); /* utility functions */ static int append_string(char **str, char const *to_append); static unsigned int fields_count(char **fields); static GATResult add_field(char ***fields, char const *key); static void release_fields(char ***fields); /* File scope variables */ /* creation and initial population of the database */ static char const *init_db_stmts[] = { "begin transaction on conflict rollback;", /* create and populate the main table */ "create table main (" "id integer primary key on conflict rollback," "key varchar(32) not null on conflict rollback," "value varchar(128)" ");", "insert or rollback into main (key, value) values('version', '10000');", "insert or rollback into main (key, value) values('datatable', 'data1');", /* create and populate the node table */ "create table nodes (" "node_id integer primary key on conflict rollback," "node_name," "parent_id" /* references key nodes(node_id) */ ");", "insert or rollback into nodes (node_name, parent_id) values('__root__', NULL);", /* create the data table */ "create table data1 (" "node_id unique," /* references key nodes(node_id) */ "data" ");", /* create and populate the meta data table (this table holds all the field names, which are in use in the current data table */ "create table metadata (" "metakey not null on conflict rollback" ");", /* the table is populated with the two existing fields (which are obviously not meta fields, but anyway ... */ "insert or rollback into metadata (metakey) values('node_id');", "insert or rollback into metadata (metakey) values('data');", "commit transaction;", 0 }; /* start a transaction */ static char const *begin_transaction_stmts[] = { "begin transaction on conflict rollback;", 0 }; /* commit an active transaction */ static char const *commit_transaction_stmts[] = { "commit transaction;", 0 }; /* External functions */ /** advertservice_db_init * * Initialise the engine and create the database, if appropriate (if it * doesn't exist). */ GATResult advertservice_db_init(GATContext context, sqlite **db) { GAT_USES_STATUS(context, "advertservice_db_init"); int result = 0; sqlite *new_db = NULL; char *err_msg = NULL; /* open/create the database file */ new_db = sqlite_open("advertservice.db", 0, &err_msg); GAT_CREATE_STATUS_IF(NULL == new_db, SQLITE_TO_GAT(SQLITE_CANTOPEN)); /* try to read the db version */ if (GAT_SUCCEEDED(GAT_CURRENT_STATUS())) { int version = 0; GAT_CREATE_STATUS(advertservice_db_get_version(new_db, &version, &err_msg)); /* database doesn't exist, recreate it */ if (GAT_FAILED(GAT_CURRENT_STATUS())) { GAT_CREATE_STATUS_UNCOND(advertservice_db_execute_sql_statements(new_db, init_db_stmts, &err_msg)); } } if (NULL == db) { GAT_CREATE_STATUS(GAT_INVALID_PARAMETER); } /* attach the database diagnostics to the status object, if appropriate */ if (GAT_FAILED(GAT_CURRENT_STATUS())) { if (NULL != err_msg) { GAT_STATUS_ADD_MESSAGE(err_msg); free(err_msg); } } else { /* return the opened database */ *db = new_db; } return GAT_RETURN_STATUS(); } /** advertservice_db_close * */ void advertservice_db_close(sqlite **db) { if (NULL != db && NULL != *db) { sqlite_close(*db); *db = NULL; } } /** advertservice_db_write_advert_data * */ GATResult advertservice_db_write_advert_data(GATContext context, sqlite *db, char const *path, char const *data, GATTable_const metadata) { GAT_USES_STATUS(context, "advertservice_db_write_advert_data"); int node_id = 0; char *err_msg = NULL; /* wrap the overall operation into a transaction */ GAT_CREATE_STATUS(advertservice_db_begin_transaction(db, &err_msg)); /* find or create the specified node inside the nodes table */ GAT_CREATE_STATUS(advertservice_db_find_node(db, path, &node_id, GATTrue, &err_msg)); /* write the data and the metadata to the data table */ GAT_CREATE_STATUS(advertservice_db_write_data(db, node_id, data, metadata, &err_msg)); /* all went ok, so commit the changes */ GAT_CREATE_STATUS(advertservice_db_commit_transaction(db, &err_msg)); /* attach the database diagnostics to the status object, if appropriate */ if (GAT_FAILED(GAT_CURRENT_STATUS()) && NULL != err_msg) { GAT_STATUS_ADD_MESSAGE(err_msg); free(err_msg); } return GAT_RETURN_STATUS(); } /** advertservice_db_delete_advert_data * */ GATResult advertservice_db_delete_advert_data(GATContext context, sqlite *db, char const *path) { GAT_USES_STATUS(context, "advertservice_db_delete_advert_data"); char *err_msg = NULL; int node_id = 0; /* wrap the overall operation into a transaction */ GAT_CREATE_STATUS(advertservice_db_begin_transaction(db, &err_msg)); /* find the specified node inside the nodes table */ GAT_CREATE_STATUS(advertservice_db_find_node(db, path, &node_id, GATFalse, &err_msg)); /* delete the node and its associated data */ GAT_CREATE_STATUS(advertservice_db_delete_node(db, node_id, &err_msg)); /* all went ok, so commit the changes */ GAT_CREATE_STATUS(advertservice_db_commit_transaction(db, &err_msg)); /* attach the database diagnostics to the status object, if appropriate */ if (GAT_FAILED(GAT_CURRENT_STATUS()) && NULL != err_msg) { GAT_STATUS_ADD_MESSAGE(err_msg); free(err_msg); } return GAT_RETURN_STATUS(); } /** advertservice_db_get_metadata * */ GATResult advertservice_db_get_metadata(GATContext context, sqlite *db, char const *path, GATTable *metadata) { GAT_USES_STATUS(context, "advertservice_db_get_metadata"); char *err_msg = NULL; int node_id = 0; /* find the specified node inside the nodes table */ GAT_CREATE_STATUS(advertservice_db_find_node(db, path, &node_id, GATFalse, &err_msg)); /* retrieve the associated metadata */ GAT_CREATE_STATUS(advertservice_db_get_node_metadata(db, node_id, metadata, &err_msg)); /* attach the database diagnostics to the status object, if appropriate */ if (GAT_FAILED(GAT_CURRENT_STATUS()) && NULL != err_msg) { GAT_STATUS_ADD_MESSAGE(err_msg); free(err_msg); } return GAT_RETURN_STATUS(); } /** advertservice_db_get_advertisable_data * */ GATResult advertservice_db_get_advert_data(GATContext context, sqlite *db, char const *path, char **data) { GAT_USES_STATUS(context, "advertservice_db_get_metadata"); char *err_msg = NULL; int node_id = 0; /* find the specified node inside the nodes table */ GAT_CREATE_STATUS(advertservice_db_find_node(db, path, &node_id, GATFalse, &err_msg)); /* retrieve the associated data */ GAT_CREATE_STATUS(advertservice_db_get_node_data(db, node_id, data, &err_msg)); /* attach the database diagnostics to the status object, if appropriate */ if (GAT_FAILED(GAT_CURRENT_STATUS()) && NULL != err_msg) { GAT_STATUS_ADD_MESSAGE(err_msg); free(err_msg); } return GAT_RETURN_STATUS(); } /** advertservice_db_find * */ GATResult advertservice_db_find(GATContext context, sqlite *db, GATTable_const metadata, GATList_String *paths) { GAT_USES_STATUS(context, "advertservice_db_find"); char *err_msg = NULL; GATuint32 len = GATTable_Size(metadata); int result = SQLITE_OK; if (0 != len) { GATuint32 i = 0; /* construct the field of regular expressions */ char buffer[256]; char *sql_stmt = NULL; int table_number = 0; void **keys = GATTable_GetKeys(metadata); regex_t *regexpr = (regex_t *)malloc(len * sizeof(regex_t)); GAT_CREATE_STATUS_IF(NULL == regexpr, GAT_MEMORYFAILURE); for (/**/; NULL != keys[i]; ++i) { GAT_CREATE_STATUS(GATTable_Get_String(metadata, keys[i], buffer, sizeof(buffer))); GAT_CREATE_STATUS_IF(0 != regcomp(&regexpr[i], buffer, 0), GAT_INVALID_PARAMETER); } if (GAT_SUCCEEDED(GAT_CURRENT_STATUS())) { /* register the user defined function */ result = sqlite_create_function(db, "re_match", 2, advertservice_db_re_match, regexpr); GAT_CREATE_STATUS_IF(SQLITE_OK != result, SQLITE_TO_GAT(result)); /* query the data from the current data table */ result = advertservice_db_get_datatable_number(db, &table_number, &err_msg); GAT_CREATE_STATUS_IF(SQLITE_OK != result, SQLITE_TO_GAT(result)); } /* construct the query string */ sprintf(buffer, "select node_id from data%d where ", table_number); GAT_CREATE_STATUS_IF(SQLITE_OK != (result = append_string(&sql_stmt, buffer)), SQLITE_TO_GAT(result)); for (i = 0; NULL != keys[i]; ++i) { sprintf(buffer, "%sre_match(%d, %s) = 1", (0 == i) ? "" : " and ", i, keys[i]); GAT_CREATE_STATUS_IF(SQLITE_OK != (result = append_string(&sql_stmt, buffer)), SQLITE_TO_GAT(result)); } GAT_CREATE_STATUS_IF(SQLITE_OK != (result = append_string(&sql_stmt, ";")), SQLITE_TO_GAT(result)); /* execute the query and fill the results into a new GATList_String object*/ { int nrows = 0; int fields = 0; char **values = NULL; GATList_String node_paths = NULL; result = sqlite_get_table(db, sql_stmt, &values, &nrows, &fields, &err_msg); GAT_CREATE_STATUS_IF(SQLITE_OK != result, SQLITE_TO_GAT(result)); node_paths = GATList_String_Create(); GAT_CREATE_STATUS_IF(NULL == node_paths, GAT_MEMORYFAILURE); GAT_CREATE_STATUS(advertservice_db_find_pathes(db, nrows, &values[1], node_paths, &err_msg)); if (NULL != paths) { *paths = node_paths; } else { GATList_String_Destroy(&node_paths); GAT_CREATE_STATUS(GAT_INVALID_PARAMETER); } sqlite_free_table(values); } /* free all the allocated memory */ free(sql_stmt); for (i = 0; i < len; ++i) { regfree(&regexpr[i]); } free(regexpr); GATTable_ReleaseKeys(metadata, &keys); } else { GAT_CREATE_STATUS(GAT_INVALID_PARAMETER); } /* attach the database diagnostics to the status object, if appropriate */ if (GAT_FAILED(GAT_CURRENT_STATUS()) && NULL != err_msg) { GAT_STATUS_ADD_MESSAGE(err_msg); free(err_msg); } return GAT_RETURN_STATUS(); } /* local functions */ static GATResult advertservice_db_get_version(sqlite *db, GATuint32 *version, char **err_msg) { int nrows = 0; int fields = 0; char **values = NULL; int result = sqlite_get_table(db, "select value from main where key = 'version';", &values, &nrows, &fields, err_msg); if (SQLITE_OK != result) { return SQLITE_TO_GAT(result); } assert(1 == fields && 1 == nrows); result = (1 == sscanf(values[1], "%d", version)) ? SQLITE_OK : SQLITE_NOTFOUND; sqlite_free_table(values); return SQLITE_TO_GAT(result); } /** advertservice_db_execute_sql_statements * */ static GATResult advertservice_db_execute_sql_statements(sqlite *db, char const **stmts, char **err_msg) { int result = SQLITE_OK; int i = 0; for(/**/; NULL != stmts[i]; ++i) { result = sqlite_exec(db, stmts[i], 0, 0, err_msg); if (SQLITE_OK != result) { break; } } return SQLITE_TO_GAT(result); } /** advertservice_db_begin_transaction * */ static GATResult advertservice_db_begin_transaction(sqlite *db, char **err_msg) { return advertservice_db_execute_sql_statements(db, begin_transaction_stmts, err_msg); } /** advertservice_db_commit_transaction * */ static GATResult advertservice_db_commit_transaction(sqlite *db, char **err_msg) { return advertservice_db_execute_sql_statements(db, commit_transaction_stmts, err_msg); } /** advertservice_db_find_node * */ static GATResult advertservice_db_find_node(sqlite *db, char const *pathstr, int *node_id, GATBool createnode, char **err_msg) { GATResult retval = GAT_SUCCESS; char *path = GATUtil_strdup(pathstr); if (NULL != path) { if ('/' == *path) { /* the given path must be absolute */ char *node_name = strtok(path + 1, "/"); int parent_id = 1; /* the root has the node_id == 1 */ int current_node_id = 0; while (NULL != node_name) { if (!strcmp(node_name, ".")) { /* if the node_name is '.' simply continue */ current_node_id = parent_id; } else if (!strcmp(node_name, "..")) { /* we need to step up one hierarchy level */ int nrows = 0; int fields = 0; char **values = NULL; int result = SQLITE_OK; if (1 == parent_id) { retval = GAT_INVALID_PARAMETER; break; /* there is no way to step up at root level */ } result = sqlite_get_table_printf(db, "select parent_id from nodes where node_id = '%d';", &values, &nrows, &fields, err_msg, parent_id); if (SQLITE_OK != result) { retval = SQLITE_TO_GAT(result); break; } if (1 != nrows || 1 != sscanf(values[1], "%d", &current_node_id)) { sqlite_free_table(values); retval = SQLITE_TO_GAT(SQLITE_NOTFOUND); break; } sqlite_free_table(values); } else { /* try to find this node */ int result = advertservice_db_read_node_id(db, node_name, parent_id, &current_node_id, err_msg); if (SQLITE_OK != result || 0 == current_node_id) { if (createnode) { /* this node does not exist, insert it into the nodes table */ result = sqlite_exec_printf(db, "insert or rollback into nodes " "(node_name, parent_id) values('%s', %d);", 0, 0, err_msg, node_name, parent_id); if (SQLITE_OK != result) { retval = SQLITE_TO_GAT(result); break; } /* now the node should exist! */ result = advertservice_db_read_node_id(db, node_name, parent_id, &current_node_id, err_msg); if (SQLITE_OK != result || 0 == current_node_id) { retval = SQLITE_TO_GAT(result); break; } } else { /* node doesn't exist */ retval = SQLITE_TO_GAT(SQLITE_NOTFOUND); break; } } } /* extract next node name */ node_name = strtok(NULL, "/"); if (NULL != node_name) { parent_id = current_node_id; current_node_id = 0; } else { *node_id = current_node_id; break; // this was the last path segment } } } else { retval = GAT_INVALID_PARAMETER; } free(path); } else { retval = GAT_MEMORYFAILURE; } return retval; } static int append_string(char **str, char const *to_append) { GATBool new_string = (NULL != *str) ? GATFalse : GATTrue; unsigned int len = (NULL != *str) ? strlen(*str) + 1 : 0; char *new_str = (char *)realloc(*str, len + strlen(to_append) + 1); if (NULL == new_str) { return SQLITE_NOMEM; } if (GATTrue == new_string) { new_str[0] = '\0'; } strcat(new_str, to_append); *str = new_str; return SQLITE_OK; } /** advertservice_db_write_data * */ static GATResult advertservice_db_write_data(sqlite *db, int node_id, char const *data, GATTable_const metadata, char **err_msg) { int result = SQLITE_OK; int table_number = 0; if (NULL != metadata) { void **keys = GATTable_GetKeys(metadata); result = advertservice_db_alter_table(db, (char const **)keys, err_msg); GATTable_ReleaseKeys(metadata, &keys); if (SQLITE_OK != result) { return SQLITE_TO_GAT(result); } } /* write the data in the current data table */ result = advertservice_db_get_datatable_number(db, &table_number, err_msg); if (SQLITE_OK != result) { return SQLITE_TO_GAT(result); } result = sqlite_exec_printf(db, "insert or replace into data%d (node_id, data) values('%d', '%q');", 0, 0, err_msg, table_number, node_id, data); if (SQLITE_OK != result) { return SQLITE_TO_GAT(result); } /* insert the metadata into the new data table */ if (NULL != metadata) { void **keys = GATTable_GetKeys(metadata); if (NULL != keys[0]) { char buffer[256]; int i = 0; char *sql_stmt = NULL; sprintf(buffer, "update or rollback data%d set ", table_number); result = append_string(&sql_stmt, buffer); if (SQLITE_OK != result) { GATTable_ReleaseKeys(metadata, &keys); return SQLITE_TO_GAT(result); } for (i = 0; NULL != keys[i]; ++i) { char value[256]; if (GAT_SUCCEEDED(GATTable_Get_String(metadata, keys[i], value, sizeof(value)))) { sprintf(buffer, "%s%s = '%s'", (0 == i) ? "" : ", ", keys[i], value); result = append_string(&sql_stmt, buffer); if (SQLITE_OK != result) { GATTable_ReleaseKeys(metadata, &keys); return SQLITE_TO_GAT(result); } } } GATTable_ReleaseKeys(metadata, &keys); sprintf(buffer, " where node_id = '%d';", node_id); result = append_string(&sql_stmt, buffer); if (SQLITE_OK != result) { return SQLITE_TO_GAT(result); } result = sqlite_exec(db, sql_stmt, 0, 0, err_msg); free(sql_stmt); } else { /* at least one metadata key/value pair should be supplied */ GATTable_ReleaseKeys(metadata, &keys); return GAT_INVALID_PARAMETER; } } return SQLITE_TO_GAT(result); } /** advertservice_db_read_node_id * */ static int advertservice_db_read_node_id(sqlite *db, char const *node_name, int parent_id, int *node_id, char **err_msg) { int result = SQLITE_OK; int nrows = 0; int fields = 0; char **values = NULL; result = sqlite_get_table_printf(db, "select node_id from nodes " "where node_name = '%s' and parent_id = %d", &values, &nrows, &fields, err_msg, node_name, parent_id); if (SQLITE_OK == result && 0 != nrows) { assert(1 == nrows && 1 == fields); result = (1 == sscanf(values[1], "%d", node_id)) ? SQLITE_OK : SQLITE_NOTFOUND; } sqlite_free_table(values); return result; } static unsigned int fields_count(char **fields) { unsigned int i = 0; if (NULL != fields) { while(NULL != fields[i]) { ++i; } } return i; } static GATResult add_field(char ***fields, char const *key) { unsigned int count = fields_count(*fields); char **new_fields = (char **)realloc(*fields, (count + 2) * sizeof(char *)); if (NULL == new_fields) { return GAT_MEMORYFAILURE; } *fields = new_fields; (*fields)[count] = GATUtil_strdup(key); if (NULL == (*fields)[count]) { return GAT_MEMORYFAILURE; } (*fields)[count+1] = NULL; return GAT_SUCCESS; } static void release_fields(char ***fields) { if (NULL != fields && NULL != *fields) { int i = 0; for (/**/; NULL != (*fields)[i]; ++i) { free((*fields)[i]); } free(*fields); *fields = NULL; } } /** advertservice_db_alter_table * * Since the SQLite database doesn't support the ALTER TABLE SQL command, we * have to create a new table with the required structure and must copy over * the old data to the new table. The old table is dropped afterwards. */ static int advertservice_db_alter_table(sqlite *db, char const **keys, char **err_msg) { int result = SQLITE_OK; int old_fields_nrows = 0; int old_fields_count = 0; char **old_fields = NULL; char **new_fields = NULL; int i = 0; int j = 1; int table_number = 0; /* get the names of the fields currently in use */ result = sqlite_get_table(db, "select metakey from metadata;", &old_fields, &old_fields_nrows, &old_fields_count, err_msg); if (SQLITE_OK != result || 0 == old_fields_nrows) { return result; } /* find the key names, which are new to the metadata table */ for (/**/; NULL != keys[i] ; ++i) { GATBool found = GATFalse; for (j = 1; j < old_fields_nrows + old_fields_count; ++j) { if (!strcmp(keys[i], old_fields[j])) { found = GATTrue; break; } } /* if this key name isn't known to us, store it */ if (GATFalse == found) { add_field(&new_fields, keys[i]); } } if (NULL == new_fields) { /* no new fields required ! */ sqlite_free_table(old_fields); return SQLITE_OK; } /* generate the name of the new data table */ result = advertservice_db_get_datatable_number(db, &table_number, err_msg); if (SQLITE_OK != result) { sqlite_free_table(old_fields); release_fields(&new_fields); return result; } /* create the new table */ result = advertservice_db_create_datatable(db, &old_fields[1], old_fields_nrows, new_fields, table_number+1, err_msg); if (SQLITE_OK != result) { sqlite_free_table(old_fields); release_fields(&new_fields); return result; } /* copy over the data to the new table */ result = advertservice_db_copy_datatable(db, &old_fields[1], old_fields_nrows, table_number, err_msg); sqlite_free_table(old_fields); if (SQLITE_OK != result) { release_fields(&new_fields); return result; } /* insert the new field names into the meta table */ result = advertservice_db_add_metadata(db, new_fields, err_msg); release_fields(&new_fields); if (SQLITE_OK != result) { return result; } /* drop the original table */ result = sqlite_exec_printf(db, "drop table data%d;", 0, 0, err_msg, table_number); if (SQLITE_OK != result) { return result; } /* save the name of the new table in the main table */ result = sqlite_exec_printf(db, "update or rollback main set value = 'data%d' where key = 'datatable';", 0, 0, err_msg, table_number + 1); if (SQLITE_OK != result) { return result; } return result; } /** advertservice_db_create_datatable * */ static int advertservice_db_create_datatable(sqlite *db, char **old_fields, int count, char **new_fields, int table_number, char **err_msg) { int result = SQLITE_OK; char *sql_stmt = NULL; char buffer[256]; int i = 0; /* construct the sql statement */ sprintf(buffer, "create table data%d (", table_number); result = append_string(&sql_stmt, buffer); if (SQLITE_OK != result) { return result; } /* append the old meta fields */ for (/**/; i < count; ++i) { if (!strcmp(old_fields[i], "node_id") || !strcmp(old_fields[i], "data")) { continue; } sprintf(buffer, "%s, ", old_fields[i]); result = append_string(&sql_stmt, buffer); if (SQLITE_OK != result) { free(sql_stmt); return result; } } /* append the new meta fields */ for (i = 0; NULL != new_fields[i]; ++i) { sprintf(buffer, "%s, ", new_fields[i]); result = append_string(&sql_stmt, buffer); if (SQLITE_OK != result) { free(sql_stmt); return result; } } /* append the required data fields */ result = append_string(&sql_stmt, "node_id unique, data);"); if (SQLITE_OK != result) { return result; } /* execute the constructed sql statement */ result = sqlite_exec(db, sql_stmt, 0, 0, err_msg); free (sql_stmt); return result; } /** advertservice_db_copy_datatable * */ static int advertservice_db_copy_datatable(sqlite *db, char **old_fields, int count, int table_number, char **err_msg) { int result = SQLITE_OK; char buffer[256]; char *sql_stmt = NULL; int i = 0; sprintf(buffer, "insert or rollback into data%d (", table_number+1); result = append_string(&sql_stmt, buffer); if (SQLITE_OK != result) { return result; } /* append the old meta fields */ for (/**/; i < count; ++i) { if (!strcmp(old_fields[i], "node_id") || !strcmp(old_fields[i], "data")) { continue; } sprintf(buffer, "%s, ", old_fields[i]); result = append_string(&sql_stmt, buffer); if (SQLITE_OK != result) { free(sql_stmt); return result; } } result = append_string(&sql_stmt, "node_id, data) select "); if (SQLITE_OK != result) { return result; } for (i = 0; i < count; ++i) { if (!strcmp(old_fields[i], "node_id") || !strcmp(old_fields[i], "data")) { continue; } sprintf(buffer, "%s, ", old_fields[i]); result = append_string(&sql_stmt, buffer); if (SQLITE_OK != result) { free(sql_stmt); return result; } } sprintf(buffer, "node_id, data from data%d;", table_number); result = append_string(&sql_stmt, buffer); if (SQLITE_OK != result) { return result; } /* execute the constructed sql command */ result = sqlite_exec(db, sql_stmt, 0, 0, err_msg); free(sql_stmt); return result; } /** advertservice_db_add_metadata * */ static int advertservice_db_add_metadata(sqlite *db, char **new_fields, char **err_msg) { int result = SQLITE_OK; int i = 0; for (i = 0; NULL != new_fields[i]; ++i) { result = sqlite_exec_printf(db, "insert or rollback into metadata (metakey) values('%s');", 0, 0, err_msg, new_fields[i]); if (SQLITE_OK != result) { break; } } return result; } /** advertservice_db_get_datatable_number * */ static int advertservice_db_get_datatable_number(sqlite *db, int *number, char **err_msg) { int result = SQLITE_OK; int nrows = 0; int fields = 0; char **values = NULL; *number = 0; /* generate the name of the new data table */ result = sqlite_get_table_printf(db, "select value from main where key = 'datatable';", &values, &nrows, &fields, err_msg); if (SQLITE_OK != result || 1 != nrows) { return result; } if (1 != sscanf(values[1], "data%d", number)) { sqlite_free_table(values); return SQLITE_NOTFOUND; } sqlite_free_table(values); return SQLITE_OK; } /** advertservice_db_delete_node * */ static GATResult advertservice_db_delete_node(sqlite *db, int node_id, char **err_msg) { int result = SQLITE_OK; int nrows = 0; int fields = 0; char **values = NULL; int table_number = 0; /* delete the node, if it has no child nodes */ result = sqlite_get_table_printf(db, "select node_id from nodes where parent_id = '%d';", &values, &nrows, &fields, err_msg, node_id); sqlite_free_table(values); if (SQLITE_OK != result) { return SQLITE_TO_GAT(result); } if (0 == nrows) { /* this node has no child nodes */ result = sqlite_exec_printf(db, "delete from nodes where node_id = '%d';", 0, 0, err_msg, node_id); if (SQLITE_OK != result) { return SQLITE_TO_GAT(result); } /* FIXME: here we should remove all empty nodes above the node to delete */ } /* get the current name of the data table */ result = advertservice_db_get_datatable_number(db, &table_number, err_msg); if (SQLITE_OK != result) { return SQLITE_TO_GAT(result); } /* delete the corresponding data from the data table */ result = sqlite_exec_printf(db, "delete from data%d where node_id = '%d';", 0, 0, err_msg, table_number, node_id); /* FIXME: here we should remove the fields (metadata keys), which aren't used anymore after deleting the data record for this node */ return SQLITE_TO_GAT(result); } /** advertservice_db_get_node_metadata * */ static GATResult advertservice_db_get_node_metadata(sqlite *db, int node_id, GATTable *metadata, char **err_msg) { GATResult retval = GAT_SUCCESS; int result = SQLITE_OK; int table_number = 0; int nrows = 0; int fields = 0; char **values = NULL; GATTable table = NULL; int i = 0; /* get the current name of the data table */ result = advertservice_db_get_datatable_number(db, &table_number, err_msg); if (SQLITE_OK != result) { return SQLITE_TO_GAT(result); } /* get the complete record for the given node */ result = sqlite_get_table_printf(db, "select * from data%d where node_id = '%d';", &values, &nrows, &fields, err_msg, table_number, node_id); if (SQLITE_OK != result) { return SQLITE_TO_GAT(result); } /* create the metadata table */ table = GATTable_Create(); if (NULL == table) { sqlite_free_table(values); return GAT_MEMORYFAILURE; } /* copy all the non empty metadata values into the table */ for (i = 0; i < fields; ++i) { /* skip the standard fields */ if (NULL == values[i+fields] || /* skip fields with NULL value */ !strcmp(values[i], "node_id") || !strcmp(values[i], "data")) { continue; } retval = GATTable_Add_String(table, values[i], values[i+fields]); if (GAT_FAILED(retval)) { break; } } /* return the filled metadata Table */ if (GAT_SUCCEEDED(retval)) { if (NULL != metadata) { *metadata = table; } else { retval = GAT_INVALID_PARAMETER; GATTable_Destroy(&table); } } else { GATTable_Destroy(&table); } sqlite_free_table(values); return retval; } /** advertservice_db_get_node_metadata * */ static GATResult advertservice_db_get_node_data(sqlite *db, int node_id, char **advert_data, char **err_msg) { GATResult retval = GAT_SUCCESS; int result = SQLITE_OK; int table_number = 0; int nrows = 0; int fields = 0; char **values = NULL; char *data = NULL; /* get the current name of the data table */ result = advertservice_db_get_datatable_number(db, &table_number, err_msg); if (SQLITE_OK != result) { return SQLITE_TO_GAT(result); } /* get the data for the given node */ result = sqlite_get_table_printf(db, "select data from data%d where node_id = '%d';", &values, &nrows, &fields, err_msg, table_number, node_id); if (SQLITE_OK != result) { return SQLITE_TO_GAT(result); } /* simply copy the data from the resultset */ data = GATUtil_strdup(values[1]); if (NULL == data) { return GAT_MEMORYFAILURE; } /* return the filled metadata Table */ if (GAT_SUCCEEDED(retval)) { if (NULL != advert_data) { *advert_data = data; } else { retval = GAT_INVALID_PARAMETER; free(data); } } else { free(data); } sqlite_free_table(values); return retval; } /** advertservice_db_re_match * * User defined SQL function re_match() */ static void advertservice_db_re_match(sqlite_func *func, int argc, const char **argv) { if (2 != argc) { sqlite_set_result_error(func, "re_match: expected exactly 2 parameters", SQLITE_MISUSE); } else { int result = REG_NOMATCH; int regexpr_num = 0; regex_t *regexpr = NULL; if (1 == sscanf(argv[0], "%d", &regexpr_num)) { regexpr = (regex_t *)sqlite_user_data(func); result = regexec(&regexpr[regexpr_num], argv[1], 0, NULL, 0); if (REG_NOERROR != result && REG_NOMATCH != result) { /* generate an appropriate regex error message */ char buffer[512]; char error_msg[256]; GATuint32 written = 0; GATResult retval = GATSelf_ResolveErrorMessage(REGEX_TO_GAT(result), error_msg, sizeof(error_msg), &written); if (GAT_SUCCEEDED(retval)) { sprintf(buffer, "re_match: unexpected regular expression error: %d (%s)", result, error_msg); } else { sprintf(buffer, "re_match: unexpected regular expression error: %d (unknown error)", result); } sqlite_set_result_error(func, buffer, SQLITE_MISUSE); } else { /* all went well, return the result */ sqlite_set_result_int(func, (REG_NOERROR == result) ? 1 : 0); } } else { sqlite_set_result_error(func, "re_match: first parameter should be of integer type", SQLITE_MISUSE); } } } /** advertservice_db_find_pathes * */ static GATResult advertservice_db_find_pathes(sqlite *db, int nrows, char **values, GATList_String node_paths, char **err_msg) { GATResult retval = SQLITE_TO_GAT(SQLITE_NOTFOUND); int i = 0; if (0 < nrows) { /* resolve all given node_id's */ for (/**/; i < nrows; ++i) { char *path = NULL; retval = advertservice_db_construct_path(db, values[i], &path, err_msg); if (GAT_SUCCEEDED(retval)) { /* remove the trailing '/' */ path[strlen(path)-1] = '\0'; GATList_String_Insert(node_paths, GATList_String_End(node_paths), path); } free(path); } } return retval; } /** advertservice_db_construct_path * */ static GATResult advertservice_db_construct_path(sqlite *db, char *node_id_str, char **path, char **err_msg) { GATResult retval = GAT_SUCCESS; int node_id = 0; if (1 != sscanf(node_id_str, "%d", &node_id)) { /* node_id's should be integer! */ retval = GAT_INVALID_PARAMETER; } else if (1 == node_id) { /* root node */ if (SQLITE_OK != append_string(path, "/")) { retval = GAT_MEMORYFAILURE; } } else { /* intermediate node, try to find the parent node and recure ... */ int nrows = 0; int fields = 0; char **values = NULL; int result = sqlite_get_table_printf(db, "select node_name, parent_id from nodes where node_id = '%d';", &values, &nrows, &fields, err_msg, node_id); if (SQLITE_OK != result) { retval = SQLITE_TO_GAT(result); } else if (1 != nrows) { /* node was not found in nodes table */ retval = SQLITE_TO_GAT(SQLITE_NOTFOUND); /* should not happen! */ } else { /* values[3]: parent_id */ retval = advertservice_db_construct_path(db, values[3], path, err_msg); if (GAT_SUCCEEDED(retval)) { /* values[2]: node_name */ if (SQLITE_OK != append_string(path, values[2]) || SQLITE_OK != append_string(path, "/")) { retval = GAT_MEMORYFAILURE; } } } /* free the allocated table data */ sqlite_free_table(values); } return retval; }