/**
* ui_formgrid_handle_user_prefs
* @formgrid: a #UiFormGrid widget
* @tcnc: (allow-none): a #TConnection, or %NULL to let @formgrid determine it itself
* @stmt: the #GdaStatement which has been executed to produce the #GdaDataModel displayed in @formgrid
*
* Takes into account the UI preferences of the user
*/
void
ui_formgrid_handle_user_prefs (UiFormGrid *formgrid, TConnection *tcnc, GdaStatement *stmt)
{
g_return_if_fail (UI_IS_FORMGRID (formgrid));
if (tcnc)
g_return_if_fail (T_IS_CONNECTION (tcnc));
else {
tcnc = get_t_connection (formgrid);
if (!tcnc)
return;
}
if (stmt)
g_return_if_fail (GDA_IS_STATEMENT (stmt));
else
return;
GdaSqlStatement *sqlst;
g_object_get ((GObject*) stmt, "structure", &sqlst, NULL);
if (!sqlst)
return;
GError *lerror = NULL;
if (((sqlst->stmt_type != GDA_SQL_STATEMENT_SELECT) &&
(sqlst->stmt_type != GDA_SQL_STATEMENT_COMPOUND)) ||
!t_connection_normalize_sql_statement (tcnc, sqlst, &lerror)) {
if (lerror)
g_print ("[%s]\n", lerror->message);
goto out;
}
handle_user_prefs_for_sql_statement (formgrid, tcnc, sqlst);
out:
gda_sql_statement_free (sqlst);
}
/**
* gda_sql_statement_insert_take_select
* @stmt: a #GdaSqlStatement pointer
* @select: a SELECT or COMPOUND #GdaSqlStatement pointer
*
* Specifies a SELECT statement, the values inserted will be the result set of @select. @select's
* ownership is transferred to
* @stmt (which means @stmt is then responsible for freeing it when no longer needed).
*/
void
gda_sql_statement_insert_take_select (GdaSqlStatement *stmt, GdaSqlStatement *select)
{
GdaSqlStatementInsert *insert = (GdaSqlStatementInsert *) stmt->contents;
GdaSqlAnyPart *part;
part = GDA_SQL_ANY_PART (select->contents);
select->contents = NULL;
gda_sql_statement_free (select);
insert->select = _gda_sql_statement_compound_reduce (part);
gda_sql_any_part_set_parent (insert->select, insert);
}
/**
* gda_sql_expr_take_select
* @expr: a #GdaSqlExpr structure
* @stmt: a #GdaSqlStatement holding the #GdaSqlStatementSelect to take from
*
* Sets the expression's parent to the #GdaSqlStatementSelect held by @stmt. After
* calling this function @stmt is freed.
*
*/
void
gda_sql_expr_take_select (GdaSqlExpr *expr, GdaSqlStatement *stmt)
{
if (stmt) {
GdaSqlAnyPart *part;
part = GDA_SQL_ANY_PART (stmt->contents);
stmt->contents = NULL;
gda_sql_statement_free (stmt);
expr->select = _gda_sql_statement_compound_reduce (part);
gda_sql_any_part_set_parent (expr->select, expr);
}
}
/*
* rewrite_statement
*
* If @stmt contains some variables for which the g_type is undefined, try to adjust it
*
* Returns: a new #GdaStatement if no error occurred
*/
static GdaStatement *
rewrite_statement (GdaReportEngine *engine, RunContext *context,
GdaStatement *stmt, GError **error)
{
ForeachData fdata;
fdata.engine = engine;
fdata.context = context;
GdaSqlStatement *sql_st;
g_object_get (G_OBJECT (stmt), "structure", &sql_st, NULL);
if (!gda_sql_any_part_foreach (GDA_SQL_ANY_PART (sql_st->contents),
(GdaSqlForeachFunc) rewrite_statement_foreach_func, &fdata, error)) {
gda_sql_statement_free (sql_st);
return NULL;
}
GdaStatement *out_stmt;
out_stmt = (GdaStatement*) g_object_new (GDA_TYPE_STATEMENT, "structure", sql_st, NULL);
gda_sql_statement_free (sql_st);
return out_stmt;
}
/**
* gda_select_alter_select_for_empty:
* @stmt: a SELECT #GdaStatement
* @error: (allow-none): a place to store errors, or %NULL
*
* Creates a new #GdaStatement, selecting the same data as @stmt, but which always returns an
* empty (no row) data model. This is use dy database providers' implementations.
*
* Returns: (transfer full): a new #GdaStatement
*/
GdaStatement *
gda_select_alter_select_for_empty (GdaStatement *stmt, G_GNUC_UNUSED GError **error)
{
GdaStatement *estmt;
GdaSqlStatement *sqlst;
GdaSqlStatementSelect *stsel;
g_assert (gda_statement_get_statement_type (stmt) == GDA_SQL_STATEMENT_SELECT);
g_object_get (G_OBJECT (stmt), "structure", &sqlst, NULL);
g_assert (sqlst);
if (sqlst->sql) {
g_free (sqlst->sql);
sqlst->sql = NULL;
}
stsel = (GdaSqlStatementSelect*) sqlst->contents;
/* set the WHERE condition to "1 = 0" */
GdaSqlExpr *expr, *cond = stsel->where_cond;
GdaSqlOperation *op;
if (cond)
gda_sql_expr_free (cond);
cond = gda_sql_expr_new (GDA_SQL_ANY_PART (stsel));
stsel->where_cond = cond;
op = gda_sql_operation_new (GDA_SQL_ANY_PART (cond));
cond->cond = op;
op->operator_type = GDA_SQL_OPERATOR_TYPE_EQ;
expr = gda_sql_expr_new (GDA_SQL_ANY_PART (op));
op->operands = g_slist_prepend (NULL, expr);
g_value_set_int ((expr->value = gda_value_new (G_TYPE_INT)), 1);
expr = gda_sql_expr_new (GDA_SQL_ANY_PART (op));
op->operands = g_slist_prepend (op->operands, expr);
g_value_set_int ((expr->value = gda_value_new (G_TYPE_INT)), 0);
/* replace any selected field which has a parameter with NULL */
gda_sql_any_part_foreach (GDA_SQL_ANY_PART (stsel), (GdaSqlForeachFunc) param_to_null_foreach,
NULL, NULL);
/* create new statement */
estmt = g_object_new (GDA_TYPE_STATEMENT, "structure", sqlst, NULL);
gda_sql_statement_free (sqlst);
return estmt;
}
static void
_midgard_query_select_execute (MidgardExecutable *iface, gboolean async, GError **error)
{
g_return_if_fail (iface != NULL);
MidgardQuerySelect *self = MIDGARD_QUERY_SELECT (iface);
MidgardQueryExecutor *executor = MIDGARD_QUERY_EXECUTOR (self);
GError *err = NULL;
midgard_validable_validate (MIDGARD_VALIDABLE (self), &err);
if (err) {
g_propagate_error (error, err);
return;
}
MidgardDBObjectClass *klass = executor->priv->storage->priv->klass;
MidgardConnection *mgd = executor->priv->mgd;
GdaConnection *cnc = mgd->priv->connection;
GdaSqlStatement *sql_stm;
GdaSqlStatementSelect *sss;
sql_stm = gda_sql_statement_new (GDA_SQL_STATEMENT_SELECT);
sss = (GdaSqlStatementSelect*) sql_stm->contents;
g_assert (GDA_SQL_ANY_PART (sss)->type == GDA_SQL_ANY_STMT_SELECT);
MIDGARD_QUERY_EXECUTOR (self)->priv->stmt = sql_stm;
sss->from = gda_sql_select_from_new (GDA_SQL_ANY_PART (sss));
/* Initialize top base expresion and operation with default AND operator type */
GdaSqlExpr *base_where = gda_sql_expr_new (GDA_SQL_ANY_PART (sss));
GdaSqlOperation *base_operation = gda_sql_operation_new (GDA_SQL_ANY_PART (base_where));
base_operation->operator_type = GDA_SQL_OPERATOR_TYPE_AND;
base_where->cond = base_operation;
gda_sql_statement_select_take_where_cond (sql_stm, base_where);
/* Create targets (FROM) */
GdaSqlSelectTarget *s_target = gda_sql_select_target_new (GDA_SQL_ANY_PART (sss->from));
s_target->table_name = g_strdup (midgard_core_class_get_table (klass));
s_target->as = g_strdup_printf ("t%d", ++MIDGARD_QUERY_EXECUTOR (self)->priv->tableid);
MIDGARD_QUERY_EXECUTOR (self)->priv->table_alias = g_strdup (s_target->as);
gda_sql_select_from_take_new_target (sss->from, s_target);
/* Set target expression */
GdaSqlExpr *texpr = gda_sql_expr_new (GDA_SQL_ANY_PART (s_target));
GValue *tval = g_new0 (GValue, 1);
g_value_init (tval, G_TYPE_STRING);
g_value_set_string (tval, s_target->table_name);
texpr->value = tval;
s_target->expr = texpr;
/* Add fields for all properties registered per class (SELECT a,b,c...) */
klass->dbpriv->add_fields_to_select_statement (klass, mgd, sss, s_target->as);
GdaSqlExpr *where = sss->where_cond;
GdaSqlOperation *operation = where->cond;
/* Add joins, LEFT JOIN tbl2 ON... */
__query_select_add_joins (MIDGARD_QUERY_SELECT (self), operation, &err);
if (err) {
g_propagate_error (error, err);
goto return_false;
}
/* Add constraints' conditions (WHERE a=1, b=2...) */
if (MIDGARD_QUERY_EXECUTOR (self)->priv->constraint) {
MIDGARD_QUERY_CONSTRAINT_SIMPLE_GET_INTERFACE (MIDGARD_QUERY_EXECUTOR (self)->priv->constraint)->priv->add_conditions_to_statement (MIDGARD_QUERY_EXECUTOR (self), MIDGARD_QUERY_EXECUTOR (self)->priv->constraint, sql_stm, base_where, &err);
if (err) {
g_propagate_error (error, err);
goto return_false;
}
if (MIDGARD_QUERY_EXECUTOR (self)->priv->n_constraints == 1)
__add_second_dummy_constraint (sss, operation);
/* Add dummy constraint if operation has only one operand */
if (operation->operands && (g_slist_length (operation->operands) == 1))
__add_dummy_constraint (sss, operation);
} else {
/* no constraints, add dummy '1=1 AND 0<1' to satisfy top constraint group */
__add_dummy_constraint (sss, operation);
__add_second_dummy_constraint (sss, operation);
}
/* Add orders , ORDER BY t1.field... */
if (!__query_select_add_orders (executor, &err)) {
if (err)
g_propagate_error (error, err);
goto return_false;
}
/* Exclude deleted */
if (MGD_DBCLASS_METADATA_CLASS (klass) && !executor->priv->include_deleted)
__add_exclude_deleted_constraints (executor, sss, operation, klass);
/* Add limit, LIMIT x */
if (executor->priv->limit > 0) {
GdaSqlExpr *limit_expr = gda_sql_expr_new (GDA_SQL_ANY_PART (sss));
GValue *limit_val = g_new0 (GValue, 1);
g_value_init (limit_val, G_TYPE_STRING);
g_value_take_string (limit_val, g_strdup_printf ("%d", executor->priv->limit));
limit_expr->value = limit_val;
sss->limit_count = limit_expr;
}
/* Add offset, OFFSET x */
if (executor->priv->offset >= 0) {
GdaSqlExpr *offset_expr = gda_sql_expr_new (GDA_SQL_ANY_PART (sss));
GValue *offset_val = g_new0 (GValue, 1);
g_value_init (offset_val, G_TYPE_STRING);
g_value_take_string (offset_val, g_strdup_printf ("%d", executor->priv->offset));
offset_expr->value = offset_val;
sss->limit_offset = offset_expr;
}
/* Check structure */
if (!gda_sql_statement_check_structure (sql_stm, &err)) {
g_set_error (error, MIDGARD_EXECUTION_ERROR, MIDGARD_EXECUTION_ERROR_INTERNAL,
"Can't build SELECT statement: %s)", err && err->message ? err->message : _("Unknown reason"));
if (err)
g_clear_error (&err);
goto return_false;
}
/* Create statement */
GdaStatement *stmt = gda_statement_new ();
g_object_set (G_OBJECT (stmt), "structure", sql_stm, NULL);
gda_sql_statement_free (sql_stm);
sql_stm = NULL;
if (MGD_CNC_DEBUG (mgd)) {
gchar *debug_sql = gda_connection_statement_to_sql (cnc, stmt, NULL, GDA_STATEMENT_SQL_PRETTY, NULL, NULL);
g_debug ("QuerySelect: %s", debug_sql);
g_free (debug_sql);
}
/* execute statement */
GdaDataModel *model = NULL;
midgard_executable_execution_start (MIDGARD_EXECUTABLE(self));
model = gda_connection_statement_execute_select (cnc, stmt, NULL, &err);
g_object_unref (stmt);
if (!model && !err) {
g_set_error (error, MIDGARD_EXECUTION_ERROR, MIDGARD_EXECUTION_ERROR_INTERNAL,
"Execute error - Unknown reason, underlying error is NULL");
goto return_false;
}
if (err) {
g_set_error (error, MIDGARD_EXECUTION_ERROR, MIDGARD_EXECUTION_ERROR_INTERNAL,
"Execute error - %s", err->message);
g_error_free (err);
goto return_false;
}
executor->priv->results_count = gda_data_model_get_n_rows (model);
if (executor->priv->resultset && G_IS_OBJECT (executor->priv->resultset))
g_object_unref (G_OBJECT (executor->priv->resultset));
executor->priv->resultset = (gpointer) model;
return;
return_false:
if (sql_stm)
gda_sql_statement_free (sql_stm);
return;
}
static gboolean
do_test (ATest *test)
{
GdaSqlParser *parser;
GdaStatement *stmt;
GError *error = NULL;
gchar *tmp;
g_print ("** test %s\n", test->id);
parser = gda_sql_parser_new ();
GdaSet *params;
GValue *nv;
GdaHolder *holder;
stmt = gda_sql_parser_parse_string (parser, test->sql, NULL, &error);
g_object_unref (parser);
if (!stmt) {
g_print ("Parsing error: %s\n", error && error->message ? error->message : "No detail");
return FALSE;
}
if (! gda_statement_get_parameters (stmt, ¶ms, &error)) {
g_print ("Error: %s\n", error && error->message ? error->message : "No detail");
return FALSE;
}
g_assert (gda_set_set_holder_value (params, NULL, "name", "zzz"));
nv = gda_value_new_null ();
holder = gda_set_get_holder (params, "id");
g_assert (gda_holder_set_value (holder, nv, NULL));
gda_value_free (nv);
GdaSqlStatement *sqlst;
g_object_get (stmt, "structure", &sqlst, NULL);
sqlst = gda_rewrite_sql_statement_for_null_parameters (sqlst, params, NULL, &error);
if (!sqlst) {
g_print ("Rewrite error: %s\n", error && error->message ? error->message : "No detail");
return FALSE;
}
g_object_set (stmt, "structure", sqlst, NULL);
/* SQL rendering */
tmp = gda_statement_to_sql_extended (stmt, NULL, NULL, GDA_STATEMENT_SQL_PARAMS_SHORT,
NULL, &error);
if (!tmp) {
g_print ("Rendering error: %s\n", error && error->message ? error->message : "No detail");
return FALSE;
}
/*g_print ("SQL after mod: [%s]\n", tmp);*/
g_free (tmp);
tmp = gda_sql_statement_serialize (sqlst);
if (!tmp) {
g_print ("Error: gda_sql_statement_serialize() failed\n");
return FALSE;
}
else if (strcmp (test->result, tmp)) {
g_print ("Exp: [%s]\nGot: [%s]\n", test->result, tmp);
return FALSE;
}
g_free (tmp);
gda_sql_statement_free (sqlst);
g_object_unref (stmt);
return TRUE;
}
/*
* render SQL
*/
static gboolean
test2 (GError **error)
{
GdaSqlParser *parser = NULL;
GHashTable *parsers_hash;
GdaDataModel *providers_model;
gint i;
/* create parsers */
parsers_hash = g_hash_table_new (g_str_hash, g_str_equal);
providers_model = gda_config_list_providers ();
for (i = 0; i < gda_data_model_get_n_rows (providers_model); i++) {
const GValue *pname;
pname = gda_data_model_get_value_at (providers_model, 0, i, error);
if (!pname)
return FALSE;
parser = create_parser_for_provider (g_value_get_string (pname));
g_hash_table_insert (parsers_hash, g_strdup (g_value_get_string (pname)), parser);
g_print ("Created parser for provider %s\n", g_value_get_string (pname));
}
g_object_unref (providers_model);
g_hash_table_insert (parsers_hash, "", gda_sql_parser_new ());
xmlDocPtr doc;
xmlNodePtr root, node;
gchar *fname;
fname = g_build_filename (ROOT_DIR, "tests", "parser", "testdata.xml", NULL);
if (! g_file_test (fname, G_FILE_TEST_EXISTS)) {
g_set_error (error, TEST_ERROR, TEST_ERROR_GENERIC, "File '%s' does not exist\n", fname);
return FALSE;
}
doc = xmlParseFile (fname);
g_free (fname);
g_assert (doc);
root = xmlDocGetRootElement (doc);
g_assert (!strcmp ((gchar*) root->name, "testdata"));
for (node = root->children; node; node = node->next) {
if (strcmp ((gchar*) node->name, "test"))
continue;
xmlNodePtr snode;
xmlChar *sql = NULL;
xmlChar *id;
xmlChar *prov_name;
prov_name = xmlGetProp (node, BAD_CAST "provider");
if (prov_name) {
parser = g_hash_table_lookup (parsers_hash, (gchar *) prov_name);
xmlFree (prov_name);
}
else
parser = g_hash_table_lookup (parsers_hash, "");
if (!parser)
continue;
for (snode = node->children; snode && strcmp ((gchar*) snode->name, "sql"); snode = snode->next);
if (!snode)
continue;
sql = xmlNodeGetContent (snode);
if (!sql)
continue;
GdaStatement *stmt;
GError *lerror = NULL;
stmt = gda_sql_parser_parse_string (parser, sql, NULL, &lerror);
xmlFree (sql);
id = xmlGetProp (node, BAD_CAST "id");
g_print ("===== TEST %s\n", id);
if (!stmt) {
/* skip that SQL if it can't be parsed */
g_error_free (lerror);
continue;
}
else {
GdaStatement *stmt2;
gchar *rsql;
gchar *ser1, *ser2;
rsql = gda_statement_to_sql_extended (stmt, NULL, NULL, 0, NULL, &lerror);
if (!rsql) {
g_print ("REM: test '%s' can't be rendered: %s\n", id,
lerror && lerror->message ? lerror->message : "No detail");
xmlFree (id);
continue;
}
/*g_print ("--> rendered SQL: %s\n", rsql);*/
stmt2 = gda_sql_parser_parse_string (parser, rsql, NULL, error);
if (!stmt2)
return FALSE;
GdaSqlStatement *sqlst;
g_object_get (G_OBJECT (stmt), "structure", &sqlst, NULL);
g_free (sqlst->sql);
sqlst->sql = NULL;
ser1 = gda_sql_statement_serialize (sqlst);
gda_sql_statement_free (sqlst);
g_object_get (G_OBJECT (stmt2), "structure", &sqlst, NULL);
g_free (sqlst->sql);
sqlst->sql = NULL;
ser2 = gda_sql_statement_serialize (sqlst);
gda_sql_statement_free (sqlst);
if (strcmp (ser1, ser2)) {
g_set_error (error, TEST_ERROR, TEST_ERROR_GENERIC,
"Statement failed, ID: %s\nSQL: %s\nSER1: %s\nSER2 :%s",
id, rsql, ser1, ser2);
g_free (ser1);
g_free (ser2);
return FALSE;
}
g_free (rsql);
g_free (ser1);
g_free (ser2);
g_object_unref (stmt);
g_object_unref (stmt2);
}
xmlFree (id);
if (lerror)
g_error_free (lerror);
}
g_object_unref (parser);
return TRUE;
}
int
main (int argc, char *argv[])
{
gda_init ();
GdaSqlBuilder *b;
/* INSERT INTO customers (e, f, g) VALUES (##p1::string, 15, 'joe') */
b = gda_sql_builder_new (GDA_SQL_STATEMENT_INSERT);
gda_sql_builder_set_table (b, "customers");
gda_sql_builder_add_field_value_id (b,
gda_sql_builder_add_id (b, "e"),
gda_sql_builder_add_param (b, "p1", G_TYPE_STRING, FALSE));
gda_sql_builder_add_field_value_id (b,
gda_sql_builder_add_id (b, "f"),
gda_sql_builder_add_expr (b, NULL, G_TYPE_INT, 15));
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "g"),
gda_sql_builder_add_expr (b, NULL, G_TYPE_STRING, "joe"));
render_as_sql (b);
g_object_unref (b);
/* UPDATE products set ref='A0E''FESP' WHERE id = 14 */
b = gda_sql_builder_new (GDA_SQL_STATEMENT_UPDATE);
gda_sql_builder_set_table (b, "products");
gda_sql_builder_add_field_value (b, "ref", G_TYPE_STRING, "A0E'FESP");
GdaSqlBuilderId id_field = gda_sql_builder_add_id (b, "id");
GdaSqlBuilderId id_value = gda_sql_builder_add_expr (b, NULL, G_TYPE_INT, 14);
GdaSqlBuilderId id_cond = gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_EQ, id_field, id_value, 0);
gda_sql_builder_set_where (b, id_cond);
render_as_sql (b);
/* reuse the same GdaSqlBuilder object to change the WHERE condition to: WHERE id = ##theid::int */
gda_sql_builder_set_where (b,
gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_EQ,
id_field,
gda_sql_builder_add_param (b, "theid", G_TYPE_INT, FALSE),
0));
render_as_sql (b);
g_object_unref (b);
/* DELETE FROM items WHERE id = ##theid::int */
b = gda_sql_builder_new (GDA_SQL_STATEMENT_DELETE);
gda_sql_builder_set_table (b, "items");
id_field = gda_sql_builder_add_id (b, "id");
GdaSqlBuilderId id_param = gda_sql_builder_add_param (b, "theid", G_TYPE_INT, FALSE);
id_cond = gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_EQ, id_field, id_param, 0);
gda_sql_builder_set_where (b, id_cond);
render_as_sql (b);
g_object_unref (b);
/*
* The next statement shows automatic quoting of reserved SQL keywords (DATE and SELECT here)
*
* SELECT c."date", name, date AS person FROM "select" as c, orders
*/
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
GdaSqlBuilderId id_table = gda_sql_builder_add_id (b, "select"); /* SELECT is an SQL reserved keyword */
GdaSqlBuilderId id_target1 = gda_sql_builder_select_add_target_id (b, id_table, "c");
GdaSqlBuilderId id_target2 = gda_sql_builder_select_add_target_id (b,
gda_sql_builder_add_id (b, "orders"),
NULL);
GdaSqlBuilderId id_join = gda_sql_builder_select_join_targets (b, id_target1, id_target2, GDA_SQL_SELECT_JOIN_INNER, 0);
gda_sql_builder_add_field_value_id (b,
gda_sql_builder_add_id (b, "c.date"), 0); /* DATE is an SQL reserved keyword */
gda_sql_builder_add_field_value_id (b,
gda_sql_builder_add_id (b, "name"),
gda_sql_builder_add_id (b, "person"));
render_as_sql (b);
/* reuse the same GdaSqlBuilder object to change the INNER join's condition */
gda_sql_builder_join_add_field (b, id_join, "id");
render_as_sql (b);
g_object_unref (b);
/* SELECT myfunc (a, 5, 'Joe') FROM mytable */
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
gda_sql_builder_select_add_target_id (b,
gda_sql_builder_add_id (b, "mytable"),
NULL);
GdaSqlBuilderId id_function_myfunc = gda_sql_builder_add_function (b, "myfunc",
gda_sql_builder_add_id (b, "a"),
gda_sql_builder_add_expr (b, NULL, G_TYPE_INT, 5),
gda_sql_builder_add_expr (b, NULL, G_TYPE_STRING, "Joe"),
0);
gda_sql_builder_add_field_value_id (b, id_function_myfunc, 0);
render_as_sql (b);
/* reuse the same GdaSqlBuilder object to have:
* SELECT myfunc (a, 5, 'Joe'), MAX (myfunc (a, 5, 'Joe'), b, 10) FROM mytable */
GdaSqlBuilderId id_b = gda_sql_builder_add_id (b, "b");
GdaSqlBuilderId id_b_value = gda_sql_builder_add_expr (b, NULL, G_TYPE_INT, 10);
GdaSqlBuilderId args[] = {id_function_myfunc, id_b, id_b_value};
GdaSqlBuilderId id_function_max = gda_sql_builder_add_function_v (b, "MAX", args, 3);
gda_sql_builder_add_field_value_id (b, id_function_max, 0);
render_as_sql (b);
g_object_unref (b);
/* testing identifiers which are SQL reserved keywords */
b = gda_sql_builder_new (GDA_SQL_STATEMENT_UPDATE);
gda_sql_builder_set_table (b, "select");
gda_sql_builder_add_field_value_id (b,
gda_sql_builder_add_id (b, "date"),
gda_sql_builder_add_expr (b, NULL, G_TYPE_STRING, "2009-05-27"));
id_field = gda_sql_builder_add_id (b, "id");
id_value = gda_sql_builder_add_expr (b, NULL, G_TYPE_INT, 14);
id_cond = gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_EQ, id_field, id_value, 0);
gda_sql_builder_set_where (b, id_cond);
render_as_sql (b);
g_object_unref (b);
/* testing identifiers which are SQL reserved keywords */
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
gda_sql_builder_select_add_target_id (b,
gda_sql_builder_add_id (b, "date"),
NULL);
gda_sql_builder_select_add_target_id (b,
gda_sql_builder_add_id (b, "MyTable"),
NULL);
GdaSqlBuilderId id_function = gda_sql_builder_add_function (b, "date",
gda_sql_builder_add_id (b, "a"),
gda_sql_builder_add_expr (b, NULL, G_TYPE_INT, 5),
gda_sql_builder_add_expr (b, NULL, G_TYPE_STRING, "Joe"),
0);
gda_sql_builder_add_field_value_id (b, id_function, 0);
render_as_sql (b);
g_object_unref (b);
/* Subselect: SELECT name FROM master WHERE id IN (SELECT id FROM subdata) */
GdaSqlStatement *sub;
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "id"), 0);
gda_sql_builder_select_add_target_id (b,
gda_sql_builder_add_id (b, "subdata"),
NULL);
sub = gda_sql_statement_copy (gda_sql_builder_get_sql_statement (b));
g_object_unref (b);
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "name"), 0);
gda_sql_builder_select_add_target_id (b,
gda_sql_builder_add_id (b, "master"),
NULL);
id_field = gda_sql_builder_add_id (b, "id");
GdaSqlBuilderId id_subselect = gda_sql_builder_add_sub_select (b, sub);
gda_sql_statement_free (sub);
id_cond = gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_IN, id_field, id_subselect, 0);
gda_sql_builder_set_where (b, id_cond);
render_as_sql (b);
g_object_unref (b);
/* SELECT id, name, (SELECT MAX (ts) FROM documents AS d WHERE t.id = d.topic) FROM topics AS t */
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
gda_sql_builder_select_add_target_id (b,
gda_sql_builder_add_id (b, "documents"), "d");
gda_sql_builder_add_field_value_id (b,
gda_sql_builder_add_function (b, "MAX",
gda_sql_builder_add_id (b, "ts"),
0), 0);
gda_sql_builder_set_where (b,
gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_EQ,
gda_sql_builder_add_id (b, "t.id"),
gda_sql_builder_add_id (b, "d.topic"), 0));
sub = gda_sql_statement_copy (gda_sql_builder_get_sql_statement (b));
g_object_unref (b);
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
gda_sql_builder_select_add_target_id (b,
gda_sql_builder_add_id (b, "topics"), "t");
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "id"), 0);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "name"), 0);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_sub_select (b, sub), 0);
gda_sql_statement_free (sub);
render_as_sql (b);
g_object_unref (b);
/* Subselect in INSERT: INSERT INTO customers (e, f, g) SELECT id, name, location FROM subdate */
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "id"), 0);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "name"), 0);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "location"), 0);
gda_sql_builder_select_add_target_id (b,
gda_sql_builder_add_id (b, "subdate"),
NULL);
sub = gda_sql_statement_copy (gda_sql_builder_get_sql_statement (b));
g_object_unref (b);
b = gda_sql_builder_new (GDA_SQL_STATEMENT_INSERT);
gda_sql_builder_set_table (b, "customers");
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "e"), 0);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "f"), 0);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "g"), 0);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_sub_select (b, sub), 0);
gda_sql_statement_free (sub);
render_as_sql (b);
g_object_unref (b);
/* compound: SELECT id, name FROM subdata1 UNION SELECT ident, lastname FROM subdata2 */
GdaSqlStatement *sub1, *sub2;
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "id"), 0);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "name"), 0);
gda_sql_builder_select_add_target_id (b,
gda_sql_builder_add_id (b, "subdata1"),
NULL);
sub1 = gda_sql_statement_copy (gda_sql_builder_get_sql_statement (b));
g_object_unref (b);
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "ident"), 0);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "lastname"), 0);
gda_sql_builder_select_add_target_id (b,
gda_sql_builder_add_id (b, "subdata2"),
NULL);
sub2 = gda_sql_statement_copy (gda_sql_builder_get_sql_statement (b));
g_object_unref (b);
b = gda_sql_builder_new (GDA_SQL_STATEMENT_COMPOUND);
gda_sql_builder_compound_add_sub_select (b, sub1);
gda_sql_builder_compound_add_sub_select (b, sub2);
gda_sql_statement_free (sub1);
gda_sql_statement_free (sub2);
render_as_sql (b);
g_object_unref (b);
/* SELECT CASE WHEN price < 1.200000 THEN 2 ELSE 1 END FROM data */
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
id_cond = gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_LT,
gda_sql_builder_add_id (b, "price"),
gda_sql_builder_add_expr (b, NULL, G_TYPE_FLOAT, 1.2), 0);
GdaSqlBuilderId id_case = gda_sql_builder_add_case (b,
0,
gda_sql_builder_add_expr (b, NULL, G_TYPE_INT, 1),
id_cond, gda_sql_builder_add_expr (b, NULL, G_TYPE_INT, 2),
0);
gda_sql_builder_add_field_value_id (b, id_case, 0);
gda_sql_builder_select_add_target_id (b,
gda_sql_builder_add_id (b, "data"),
NULL);
render_as_sql (b);
g_object_unref (b);
/* SELECT CASE tag WHEN 'Alpha' THEN 1 WHEN 'Bravo' THEN 2 WHEN 'Charlie' THEN 3 ELSE 0 END FROM data */
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
id_case = gda_sql_builder_add_case (b,
gda_sql_builder_add_id (b, "tag"),
gda_sql_builder_add_expr (b, NULL, G_TYPE_INT, 0),
gda_sql_builder_add_expr (b, NULL, G_TYPE_STRING, "Alpha"),
gda_sql_builder_add_expr (b, NULL, G_TYPE_INT, 1),
gda_sql_builder_add_expr (b, NULL, G_TYPE_STRING, "Bravo"),
gda_sql_builder_add_expr (b, NULL, G_TYPE_INT, 2),
gda_sql_builder_add_expr (b, NULL, G_TYPE_STRING, "Charlie"),
gda_sql_builder_add_expr (b, NULL, G_TYPE_INT, 3),
0);
gda_sql_builder_add_field_value_id (b, id_case, 0);
gda_sql_builder_select_add_target_id (b,
gda_sql_builder_add_id (b, "data"),
NULL);
render_as_sql (b);
g_object_unref (b);
/*
* SELECT people.firstname AS person, people.lastname, "date" AS birthdate, age FROM people
*/
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
gda_sql_builder_select_add_field (b, "firstname", "people", "person");
gda_sql_builder_select_add_field (b, "lastname", "people", NULL);
gda_sql_builder_select_add_field (b, "date", NULL, "birthdate");
gda_sql_builder_select_add_field (b, "age", NULL, NULL);
gda_sql_builder_select_add_target_id (b,
gda_sql_builder_add_id (b, "people"),
NULL);
render_as_sql (b);
g_object_unref (b);
/* INSERT INTO customers (f, g) VALUES (15, 'joe') */
b = gda_sql_builder_new (GDA_SQL_STATEMENT_INSERT);
gda_sql_builder_set_table (b, "customers");
gda_sql_builder_add_field_value (b, "f", G_TYPE_INT, 15);
gda_sql_builder_add_field_value (b, "g", G_TYPE_STRING, "joe");
render_as_sql (b);
g_object_unref (b);
/* Create a WHERE clause in a statement and reuse it in another one:
*
* - the first SQL built is DELETE FROM items WHERE id = ##theid::int
* - the "id = ##theid::int" is exported from the first build and imported into the final build
* - the final SQL is: SELECT id FROM mytable WHERE (name = ##thename::string) AND (id = ##theid::int)
*/
GdaSqlExpr *expr;
b = gda_sql_builder_new (GDA_SQL_STATEMENT_DELETE);
gda_sql_builder_set_table (b, "items");
id_field = gda_sql_builder_add_id (b, "id");
id_param = gda_sql_builder_add_param (b, "theid", G_TYPE_INT, FALSE);
id_cond = gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_EQ, id_field , id_param, 0);
gda_sql_builder_set_where (b, id_cond);
render_as_sql (b);
expr = gda_sql_builder_export_expression (b, id_cond);
g_object_unref (b);
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "id"), 0);
gda_sql_builder_select_add_target_id (b,
gda_sql_builder_add_id (b, "mytable"),
NULL);
id_field = gda_sql_builder_add_id (b, "name");
id_param = gda_sql_builder_add_param (b, "thename", G_TYPE_STRING, FALSE);
id_cond = gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_EQ, id_field, id_param, 0);
gda_sql_builder_set_where (b,
gda_sql_builder_add_cond (b, GDA_SQL_OPERATOR_TYPE_AND, id_cond,
gda_sql_builder_import_expression (b, expr),
0));
gda_sql_expr_free (expr);
render_as_sql (b);
g_object_unref (b);
/* Subselect: SELECT name FROM (SELECT id FROM subdata) as sub */
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "id"), 0);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "name"), 0);
gda_sql_builder_select_add_target_id (b,
gda_sql_builder_add_id (b, "subdata"),
NULL);
sub = gda_sql_statement_copy (gda_sql_builder_get_sql_statement (b));
g_object_unref (b);
b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
gda_sql_builder_add_field_value_id (b, gda_sql_builder_add_id (b, "name"), 0);
gda_sql_builder_select_add_target_id (b,
gda_sql_builder_add_sub_select (b, sub), "sub");
gda_sql_statement_free (sub);
render_as_sql (b);
g_object_unref (b);
return 0;
}
static void
actually_execute (QueryConsolePage *tconsole, const gchar *sql, GdaSet *params,
gboolean add_editor_history)
{
GdaBatch *batch;
GError *error = NULL;
const gchar *remain;
/* if a query is being executed, then show an error */
if (tconsole->priv->currently_executing) {
ui_show_error (GTK_WINDOW (gtk_widget_get_toplevel ((GtkWidget*) tconsole)),
_("A query is already being executed, "
"to execute another query, open a new connection."));
return;
}
tconsole->priv->currently_executing = TRUE;
if (!tconsole->priv->parser)
tconsole->priv->parser = t_connection_create_parser (tconsole->priv->tcnc);
batch = gda_sql_parser_parse_string_as_batch (tconsole->priv->parser, sql, &remain, &error);
if (!batch) {
ui_show_error (GTK_WINDOW (gtk_widget_get_toplevel ((GtkWidget*) tconsole)),
_("Error while parsing code: %s"),
error && error->message ? error->message : _("No detail"));
g_clear_error (&error);
return;
}
if (add_editor_history) {
/* mark the current SQL to be kept by the editor as an internal history */
query_editor_keep_current_state (tconsole->priv->editor);
}
/* actual Execution */
const GSList *stmt_list, *list;
GTimeVal start_time;
g_get_current_time (&start_time);
QueryEditorHistoryBatch *hist_batch;
hist_batch = query_editor_history_batch_new (start_time, params);
query_editor_start_history_batch (tconsole->priv->history, hist_batch);
query_editor_history_batch_unref (hist_batch);
gboolean within_transaction;
stmt_list = gda_batch_get_statements (batch);
for (list = stmt_list; list; list = list->next) {
GdaStatement *stmt;
GObject *result;
GError *lerror = NULL;
within_transaction = t_connection_get_transaction_status (tconsole->priv->tcnc) ? TRUE : FALSE;
stmt = GDA_STATEMENT (list->data);
result = t_connection_execute_statement (tconsole->priv->tcnc, stmt, params,
GDA_STATEMENT_MODEL_RANDOM_ACCESS,
NULL, &lerror);
if (result) {
QueryEditorHistoryItem *history;
GdaSqlStatement *sqlst;
g_object_get (G_OBJECT (stmt), "structure", &sqlst, NULL);
if (!sqlst->sql) {
gchar *sql;
sql = gda_statement_to_sql (stmt, NULL, NULL);
history = query_editor_history_item_new (sql, result, lerror);
g_free (sql);
}
else
history = query_editor_history_item_new (sqlst->sql, result, lerror);
g_object_unref (result);
gda_sql_statement_free (sqlst);
history->within_transaction = within_transaction;
/* display a message if a transaction has been started */
if (! history->within_transaction &&
t_connection_get_transaction_status (tconsole->priv->tcnc) &&
gda_statement_get_statement_type (stmt) != GDA_SQL_STATEMENT_BEGIN) {
browser_window_show_notice_printf (BROWSER_WINDOW (gtk_widget_get_toplevel ((GtkWidget*) tconsole)),
GTK_MESSAGE_INFO,
"QueryExecTransactionStarted",
"%s", _("A transaction has automatically been started\n"
"during this statement's execution, this usually\n"
"happens when blobs are selected (and the transaction\n"
"will have to remain opened while the blobs are still\n"
"accessible, clear the corresponding history item before\n"
"closing the transaction)."));
}
query_editor_add_history_item (tconsole->priv->history, history);
query_editor_history_item_unref (history);
browser_window_push_status (BROWSER_WINDOW (gtk_widget_get_toplevel ((GtkWidget*) tconsole)),
"QueryConsolePage", _("Statement executed"), TRUE);
}
else {
ui_show_error (GTK_WINDOW (gtk_widget_get_toplevel ((GtkWidget*) tconsole)),
_("Error executing query:\n%s"),
lerror && lerror->message ? lerror->message : _("No detail"));
g_clear_error (&lerror);
break;
}
}
g_object_unref (batch);
tconsole->priv->currently_executing = FALSE;
}
/*
* Load data from file @file into table @table
*/
gboolean
test_cnc_load_data_from_file (GdaConnection *cnc, const gchar *table, const gchar *full_file, GError **error)
{
GdaStatement *stmt = NULL;
GdaSet *params = NULL;
GdaDataModel *import;
gint nrows, ncols, i;
GdaMetaStruct *mstruct = NULL;
GSList *list;
gboolean retval = TRUE;
/* loading XML file */
import = gda_data_model_import_new_file (full_file, TRUE, NULL);
if (gda_data_model_import_get_errors (GDA_DATA_MODEL_IMPORT (import))) {
g_set_error (error, TEST_ERROR, TEST_ERROR_GENERIC, "Error loading '%s' file", full_file);
return FALSE;
}
/* retrieving meta data info */
GdaMetaDbObject *table_dbo;
GValue *name_value;
g_value_set_string ((name_value = gda_value_new (G_TYPE_STRING)), table);
mstruct = gda_meta_struct_new (gda_connection_get_meta_store (cnc), GDA_META_STRUCT_FEATURE_NONE);
table_dbo = gda_meta_struct_complement (mstruct, GDA_META_DB_TABLE,
NULL, NULL, name_value, error);
gda_value_free (name_value);
if (! table_dbo) {
retval = FALSE;
goto out;
}
/* creating INSERT statement */
GdaSqlStatement *st;
GdaSqlStatementInsert *ist;
GSList *insert_values_list = NULL;
ist = g_new0 (GdaSqlStatementInsert, 1);
GDA_SQL_ANY_PART (ist)->type = GDA_SQL_ANY_STMT_INSERT;
ist->table = gda_sql_table_new (GDA_SQL_ANY_PART (ist));
ist->table->table_name = g_strdup (table);
GdaMetaTable *mtable = GDA_META_TABLE (table_dbo);
for (list = mtable->columns; list; list = list->next) {
GdaMetaTableColumn *tcol = GDA_META_TABLE_COLUMN (list->data);
GdaSqlField *field;
/* field */
field = gda_sql_field_new (GDA_SQL_ANY_PART (ist));
field->field_name = g_strdup (tcol->column_name);
ist->fields_list = g_slist_append (ist->fields_list, field);
/* value */
GdaSqlParamSpec *pspec = g_new0 (GdaSqlParamSpec, 1);
GdaSqlExpr *expr;
pspec->name = g_strdup (tcol->column_name);
pspec->g_type = tcol->gtype;
pspec->nullok = tcol->nullok;
expr = gda_sql_expr_new (GDA_SQL_ANY_PART (ist));
expr->param_spec = pspec;
insert_values_list = g_slist_append (insert_values_list, expr);
}
ist->values_list = g_slist_append (NULL, insert_values_list);
st = gda_sql_statement_new (GDA_SQL_STATEMENT_INSERT);
st->contents = ist;
stmt = g_object_new (GDA_TYPE_STATEMENT, "structure", st, NULL);
gda_sql_statement_free (st);
g_object_unref (mstruct);
if (! gda_statement_get_parameters (stmt, ¶ms, error)) {
retval = FALSE;
goto out;
}
/* executing inserts */
nrows = gda_data_model_get_n_rows (import);
ncols = gda_data_model_get_n_columns (import);
if (!gda_connection_begin_transaction (cnc, NULL, GDA_TRANSACTION_ISOLATION_UNKNOWN, error)) {
retval = FALSE;
goto out;
}
for (i = 0; i < nrows; i++) {
gint j;
GSList *list;
for (list = params->holders, j = 0; list && (j < ncols); list = list->next, j++) {
const GValue *cvalue = gda_data_model_get_value_at (import, j, i, error);
if (!cvalue) {
gda_connection_rollback_transaction (cnc, NULL, NULL);
retval = FALSE;
goto out;
}
if (! gda_holder_set_value (GDA_HOLDER (list->data), cvalue, error)) {
gda_connection_rollback_transaction (cnc, NULL, NULL);
retval = FALSE;
goto out;
}
}
if (list || (j < ncols)) {
g_set_error (error, TEST_ERROR, TEST_ERROR_GENERIC, "%s",
"Incoherent number of columns in table and imported data");
gda_connection_rollback_transaction (cnc, NULL, NULL);
retval = FALSE;
goto out;
}
if (gda_connection_statement_execute_non_select (cnc, stmt, params, NULL, error) == -1) {
gda_connection_rollback_transaction (cnc, NULL, NULL);
retval = FALSE;
goto out;
}
}
if (! gda_connection_commit_transaction (cnc, NULL, error))
retval = FALSE;
out:
if (import)
g_object_unref (import);
if (stmt)
g_object_unref (stmt);
if (params)
g_object_unref (params);
return retval;
}
