int dbt2_sql_execute(struct db_context_t *dbc, char * query, struct sql_result_t * sql_result,
char * query_name)
{
sql_result->error = 0;
sql_result->query_running = 0;
sql_result->prefetched = 0;
if (!dbc->inTransaction)
{
if (sqlite3_exec(dbc->db, "BEGIN TRANSACTION;", NULL, NULL, NULL) != SQLITE_OK)
{
LOG_ERROR_MESSAGE("%s: BEGIN TRANSACTION;\nsqlite reports: %d %s", query_name, sqlite3_errcode(dbc->db), sqlite3_errmsg(dbc->db));
return 0;
}
dbc->inTransaction = 1;
}
if (sqlite3_prepare_v2(dbc->db, query, strlen(query)+1, &sql_result->pStmt, NULL) != SQLITE_OK)
{
LOG_ERROR_MESSAGE("%s: %s\nsqlite reports: %d %s",query_name, query,
sqlite3_errcode(dbc->db), sqlite3_errmsg(dbc->db));
sqlite3_finalize(sql_result->pStmt);
return 0;
}
sql_result->num_fields = sqlite3_column_count(sql_result->pStmt);
sql_result->query_running = 1;
dbt2_sql_fetchrow(dbc, sql_result);
if (sql_result->error)
{
return 0;
}
sql_result->prefetched = 1;
return 1;
}
int integrity(struct db_context_t *dbc, struct integrity_t *data, char ** vals, int nvals)
{
/* Input variables. */
int w_id = data->w_id;
struct sql_result_t result;
char query[256];
int W_ID=0;
dbt2_init_values(vals, nvals);
sprintf(query, INTEGRITY_1);
#ifdef DEBUG_QUERY
LOG_ERROR_MESSAGE("INTEGRITY_1 query: %s\n",query);
#endif
if (dbt2_sql_execute(dbc, query, &result, "INTEGRITY_1") && result.result_set)
{
dbt2_sql_fetchrow(dbc, &result);
vals[W_ID] = dbt2_sql_getvalue(dbc, &result, 0); //W_ID
dbt2_sql_close_cursor(dbc, &result);
if (!vals[W_ID])
{
LOG_ERROR_MESSAGE("ERROR: W_ID is NULL for query INTEGRITY_1:\n%s\n", query);
return -1;
}
if (atoi(vals[W_ID]) != w_id)
{
LOG_ERROR_MESSAGE("ERROR: Expect W_ID = %d Got W_ID = %d", w_id, atoi(vals[W_ID]));
return -1;
}
}
else //error
{
return -1;
}
dbt2_free_values(vals, nvals);
return 1;
}
int order_status(struct db_context_t *dbc, struct order_status_t *data, char ** vals, int nvals)
{
/* Input variables. */
int c_id = data->c_id;
int c_w_id = data->c_w_id;
int c_d_id = data->c_d_id;
char c_last[C_LAST_LEN+1];
char query[512];
struct sql_result_t result;
int i;
int my_c_id = 0;
int TMP_C_ID=0;
int C_FIRST = 1;
int C_MIDDLE = 2;
int MY_C_BALANCE = 3;
int C_BALANCE = 4;
int O_ID = 5;
int O_CARRIER_ID = 6;
int O_ENTRY_D = 7;
int O_OL_CNT = 8;
char * ol_i_id[15];
char * ol_supply_w_id[15];
char * ol_quantity[15];
char * ol_amount[15];
char * ol_delivery_d[15];
unsigned long skip_rows;
dbt2_init_values(vals, nvals);
dbt2_init_values(ol_i_id, 15);
dbt2_init_values(ol_supply_w_id, 15);
dbt2_init_values(ol_quantity, 15);
dbt2_init_values(ol_amount, 15);
dbt2_init_values(ol_delivery_d, 15);
snprintf(c_last, C_LAST_LEN+1, "%s", data->c_last);
if (c_id == 0)
{
sprintf(query, ORDER_STATUS_1, c_w_id, c_d_id, c_last);
#ifdef DEBUG_QUERY
LOG_ERROR_MESSAGE("ORDER_STATUS_1 %s\n", query);
#endif
if (dbt2_sql_execute(dbc, query, &result, "ORDER_STATUS_1") && result.result_set)
{
//We have to get data from middle of result set
if (result.num_rows > 1 )
{
skip_rows=result.num_rows/2;
while (skip_rows && dbt2_sql_fetchrow(dbc, &result))
{
skip_rows--;
}
}
else
{
dbt2_sql_fetchrow(dbc, &result);
}
vals[TMP_C_ID]= dbt2_sql_getvalue(dbc, &result, 0); //TMP_C_ID
dbt2_sql_close_cursor(dbc, &result);
if (!vals[TMP_C_ID])
{
LOG_ERROR_MESSAGE("ERROR: TMP_C_ID=NULL for query ORDER_STATUS_1:\n%s\n", query);
return -1;
}
my_c_id = atoi(vals[TMP_C_ID]);
}
else //error
{
return -1;
}
}
else
{
my_c_id = c_id;
vals[TMP_C_ID]=NULL;
}
sprintf(query, ORDER_STATUS_2, c_w_id, c_d_id, my_c_id);
#ifdef DEBUG_QUERY
LOG_ERROR_MESSAGE("ORDER_STATUS_2 %s\n", query);
#endif
if (dbt2_sql_execute(dbc, query, &result, "ORDER_STATUS_2") && result.result_set)
{
dbt2_sql_fetchrow(dbc, &result);
vals[C_FIRST]= dbt2_sql_getvalue(dbc, &result, 0); //C_FIRST C_MIDDLE MY_C_BALANCE C_BALANCE
vals[C_MIDDLE]= dbt2_sql_getvalue(dbc, &result, 1);
vals[MY_C_BALANCE]= dbt2_sql_getvalue(dbc, &result, 2);
vals[C_BALANCE]= dbt2_sql_getvalue(dbc, &result, 3);
//FIXME: To add checks that vars above are not null
dbt2_sql_close_cursor(dbc, &result);
}
else //error
{
return -1;
}
sprintf(query, ORDER_STATUS_3, c_w_id, c_d_id, my_c_id);
#ifdef DEBUG_QUERY
LOG_ERROR_MESSAGE("ORDER_STATUS_3 %s\n", query);
#endif
if (dbt2_sql_execute(dbc, query, &result, "ORDER_STATUS_3") && result.result_set)
{
dbt2_sql_fetchrow(dbc, &result);
vals[O_ID]= dbt2_sql_getvalue(dbc, &result, 0); //O_ID O_CARRIER_ID O_ENTRY_D O_OL_CNT
vals[O_CARRIER_ID]= dbt2_sql_getvalue(dbc, &result, 1);
vals[O_ENTRY_D]= dbt2_sql_getvalue(dbc, &result, 2);
vals[O_OL_CNT]= dbt2_sql_getvalue(dbc, &result, 3);
dbt2_sql_close_cursor(dbc, &result);
}
else //error
{
return -1;
}
sprintf(query, ORDER_STATUS_4, c_w_id, c_d_id, vals[O_ID]);
#ifdef DEBUG_QUERY
LOG_ERROR_MESSAGE("ORDER_STATUS_4 %s\n", query);
#endif
if (dbt2_sql_execute(dbc, query, &result, "ORDER_STATUS_4") && result.result_set)
{
i= 0;
while (dbt2_sql_fetchrow(dbc, &result))
{
ol_i_id[i]= dbt2_sql_getvalue(dbc, &result, 0);
ol_supply_w_id[i]= dbt2_sql_getvalue(dbc, &result, 1);
ol_quantity[i]= dbt2_sql_getvalue(dbc, &result, 2);
ol_amount[i]= dbt2_sql_getvalue(dbc, &result, 3);
ol_delivery_d[i]= dbt2_sql_getvalue(dbc, &result, 4);
i++;
}
dbt2_sql_close_cursor(dbc, &result);
}
else //error
{
return -1;
}
dbt2_free_values(vals, nvals);
dbt2_free_values(ol_i_id, 15);
dbt2_free_values(ol_supply_w_id, 15);
dbt2_free_values(ol_quantity, 15);
dbt2_free_values(ol_amount, 15);
dbt2_free_values(ol_delivery_d, 15);
return 1;
}
int delivery(struct db_context_t *dbc, struct delivery_t *data, char ** vals, int nvals)
{
/* Input variables. */
int w_id = data->w_id;
int o_carrier_id = data->o_carrier_id;
struct sql_result_t result;
char query[256];
int d_id;
int NO_O_ID=0;
int O_C_ID=1;
int OL_AMOUNT=2;
for (d_id = 1; d_id <= 10; d_id++)
{
sprintf(query, DELIVERY_1, w_id, d_id);
#ifdef DEBUG_QUERY
LOG_ERROR_MESSAGE("DELIVERY_1: %s\n",query);
#endif
if (dbt2_sql_execute(dbc, query, &result, "DELIVERY_1") && result.result_set)
{
dbt2_sql_fetchrow(dbc, &result);
vals[NO_O_ID]= (char *)dbt2_sql_getvalue(dbc, &result, 0); //NO_O_ID
dbt2_sql_close_cursor(dbc, &result);
if (!vals[NO_O_ID])
{
LOG_ERROR_MESSAGE("ERROR: NO_O_ID=NULL for query DELIVERY_1:\n%s\n", query);
//return -1;
}
}
else
{
/* Nothing to delivery for this district, try next. */
continue;
}
if (vals[NO_O_ID] && atoi(vals[NO_O_ID])>0)
{
sprintf(query, DELIVERY_2, vals[NO_O_ID], w_id, d_id);
#ifdef DEBUG_QUERY
LOG_ERROR_MESSAGE("DELIVERY_2: %s\n",query);
#endif
if (!dbt2_sql_execute(dbc, query, &result, "DELIVERY_2"))
{
return -1;
}
sprintf(query, DELIVERY_3, vals[NO_O_ID], w_id, d_id);
#ifdef DEBUG_QUERY
LOG_ERROR_MESSAGE("DELIVERY_3: %s\n",query);
#endif
if (dbt2_sql_execute(dbc, query, &result, "DELIVERY_3") && result.result_set)
{
dbt2_sql_fetchrow(dbc, &result);
vals[O_C_ID]= (char *)dbt2_sql_getvalue(dbc, &result, 0); //O_C_ID
dbt2_sql_close_cursor(dbc, &result);
if (!vals[O_C_ID])
{
LOG_ERROR_MESSAGE("DELIVERY_3:query %s\nO_C_ID= NULL", query);
//return -1;
}
}
else //error
{
return -1;
}
sprintf(query, DELIVERY_4, o_carrier_id, vals[NO_O_ID], w_id, d_id);
#ifdef DEBUG_QUERY
LOG_ERROR_MESSAGE("DELIVERY_4: query %s\n", query);
#endif
if (!dbt2_sql_execute(dbc, query, &result, "DELIVERY_4"))
{
return -1;
}
sprintf(query, DELIVERY_5, vals[NO_O_ID], w_id, d_id);
#ifdef DEBUG_QUERY
LOG_ERROR_MESSAGE("DELIVERY_5: query %s\n", query);
#endif
if (!dbt2_sql_execute(dbc, query, &result, "DELIVERY_5"))
{
return -1;
}
sprintf(query, DELIVERY_6, vals[NO_O_ID], w_id, d_id);
#ifdef DEBUG_QUERY
LOG_ERROR_MESSAGE("DELIVERY_6: query %s\n", query);
#endif
if (dbt2_sql_execute(dbc, query, &result, "DELIVERY_6") && result.result_set)
{
dbt2_sql_fetchrow(dbc, &result);
vals[OL_AMOUNT]= (char *)dbt2_sql_getvalue(dbc, &result, 0); //OL_AMOUNT
dbt2_sql_close_cursor(dbc, &result);
if (!vals[OL_AMOUNT])
{
return -1;
}
}
else //error
{
return -1;
}
snprintf(query, 250, DELIVERY_7, vals[OL_AMOUNT], vals[O_C_ID], w_id, d_id);
#ifdef DEBUG_QUERY
LOG_ERROR_MESSAGE("DELIVERY_7: query %s LEN %d\n", query, strlen(query));
#endif
if (!dbt2_sql_execute(dbc, query, &result, "DELIVERY_7"))
{
LOG_ERROR_MESSAGE("DELIVERY_7: OL_AMOUNT: |%s| O_C_ID: |%s| query %s", vals[OL_AMOUNT],
vals[O_C_ID], query);
return -1;
}
}
dbt2_free_values(vals, nvals);
}
return 1;
}
int stock_level(struct db_context_t *dbc, struct stock_level_t *data, char ** vals, int nvals)
{
/* Input variables. */
int w_id = data->w_id;
int d_id = data->d_id;
int threshold = data->threshold;
struct sql_result_t result;
int d_next_o_id = 0;
int low_stock = 0;
char query[256];
int D_NEXT_O_ID=0;
int LOW_STOCK=1;
dbt2_init_values(vals, nvals);
sprintf(query, STOCK_LEVEL_1, w_id, d_id);
#ifdef DEBUG_QUERY
LOG_ERROR_MESSAGE("STOCK_LEVEL_1 query: %s\n",query);
#endif
if (dbt2_sql_execute(dbc, query, &result, "STOCK_LEVEL_1") && result.result_set)
{
dbt2_sql_fetchrow(dbc, &result);
vals[D_NEXT_O_ID] = dbt2_sql_getvalue(dbc, &result, 0); //D_NEXT_O_ID
dbt2_sql_close_cursor(dbc, &result);
if (!vals[D_NEXT_O_ID])
{
LOG_ERROR_MESSAGE("ERROR: D_NEXT_O_ID=NULL for query STOCK_LEVEL_1:\n%s\n", query);
return -1;
}
d_next_o_id = atoi(vals[D_NEXT_O_ID]);
}
else //error
{
return -1;
}
sprintf(query, STOCK_LEVEL_2, d_id, w_id, threshold, d_next_o_id - 20,
d_next_o_id - 1);
#ifdef DEBUG_QUERY
LOG_ERROR_MESSAGE("STOCK_LEVEL_2 query: %s\n",query);
#endif
if (dbt2_sql_execute(dbc, query, &result, "STOCK_LEVEL_2") && result.result_set)
{
dbt2_sql_fetchrow(dbc, &result);
vals[LOW_STOCK]= dbt2_sql_getvalue(dbc, &result, 0); //LOW_STOCK
dbt2_sql_close_cursor(dbc, &result);
if (!vals[LOW_STOCK])
{
LOG_ERROR_MESSAGE("ERROR: LOW_STOCK=NULL for query STOCK_LEVEL_2:\n%s\n", query);
return -1;
}
low_stock = atoi(vals[LOW_STOCK]);
}
else //error
{
return -1;
}
dbt2_free_values(vals, nvals);
return 1;
}
