1 /*
2 * Copyright (C) 2007 - 2011 Vivien Malerba <malerba@gnome-db.org>
3 * Copyright (C) 2011 Murray Cumming <murrayc@murrayc.com>
4 *
5 * This program is free software; you can redistribute it and/or
6 * modify it under the terms of the GNU General Public License
7 * as published by the Free Software Foundation; either version 2
8 * of the License, or (at your option) any later version.
9 *
10 * This program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
14 *
15 * You should have received a copy of the GNU General Public License
16 * along with this program; if not, write to the Free Software
17 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
18 */
19 #include <libgda/libgda.h>
20 #include <virtual/gda-vprovider-data-model.h>
21 #include <virtual/gda-vconnection-data-model.h>
22 #include <sql-parser/gda-sql-parser.h>
23
24 static gboolean test_sql_select (GdaConnection *cnc, const gchar *sql);
25 static gboolean test_sql_non_select (GdaConnection *cnc, const gchar *sql);
26
27 GdaSqlParser *parser;
28
29 int
main()30 main ()
31 {
32 GError *error = NULL;
33 GdaConnection *cnc;
34 GdaVirtualProvider *provider;
35 GdaDataModel *xml_model, /* *csv_model, */ *rw_model;
36 GdaDataModel *proxy = NULL;
37
38 gda_init ();
39
40 parser = gda_sql_parser_new ();
41
42 provider = gda_vprovider_data_model_new ();
43 cnc = gda_virtual_connection_open (provider, NULL);
44 g_assert (GDA_IS_VCONNECTION_DATA_MODEL (cnc));
45
46 g_print ("Connection status for %s: %s\n", G_OBJECT_TYPE_NAME (cnc),
47 gda_connection_is_opened (cnc) ? "Opened" : "Closed");
48
49 /* create RW data model */
50 g_print ("Creating @rw_model from hand made array\n");
51 rw_model = gda_data_model_array_new_with_g_types (2, G_TYPE_INT, G_TYPE_STRING);
52 gda_data_model_set_column_name (rw_model, 0, "id");
53 gda_data_model_set_column_name (rw_model, 1, "a_string");
54 gda_data_model_dump (rw_model, stdout);
55
56 /* load test data model */
57 g_print ("Creating @xml_model from loading \"test_model.xml\"\n");
58 xml_model = gda_data_model_import_new_file ("test_model.xml", TRUE, NULL);
59 if (gda_data_model_get_access_flags (xml_model) & GDA_DATA_MODEL_ACCESS_CURSOR_BACKWARD)
60 gda_data_model_dump (xml_model, stdout);
61 else
62 g_print ("Data model cannot go backwards => don't yet print its contents\n");
63
64 g_print ("Creating @proxy from proxying @xml_model\n");
65 proxy = (GdaDataModel *) gda_data_proxy_new (xml_model);
66 g_object_set (G_OBJECT (proxy), "defer-sync", FALSE, NULL);
67 gda_data_model_dump (proxy, NULL);
68
69 /* load CVS data */
70 /*
71 csv_model = gda_data_model_import_new_file ("names.csv", TRUE, NULL);
72 if (gda_data_model_get_access_flags (csv_model) & GDA_DATA_MODEL_ACCESS_CURSOR_BACKWARD) {
73 gda_data_model_dump (csv_model, stdout);
74
75 GdaDataModelIter *iter;
76 iter = gda_data_model_create_iter (csv_model);
77 gda_data_model_iter_move_to_row (iter, -1);
78 for (gda_data_model_iter_move_next (iter); gda_data_model_iter_is_valid (iter);
79 gda_data_model_iter_move_next (iter)) {
80 g_print ("has row %d\n", gda_data_model_iter_get_row (iter));
81 }
82 g_object_unref (iter);
83 }
84 else
85 g_print ("Data model cannot go backwards => don't yet print its contents\n");
86 */
87
88 g_print ("Creating virtual table 'rwtable' from @rw_model\n");
89 if (!gda_vconnection_data_model_add_model (GDA_VCONNECTION_DATA_MODEL (cnc), rw_model, "rwtable", &error)) {
90 g_print ("Add RW model error: %s\n", error && error->message ? error->message : "no detail");
91 g_error_free (error);
92 error = NULL;
93 goto theend;
94 }
95 g_print ("Creating virtual table 'mytable' from @proxy\n");
96 if (!gda_vconnection_data_model_add_model (GDA_VCONNECTION_DATA_MODEL (cnc), proxy, "mytable", &error)) {
97 g_print ("Add Proxy model error: %s\n", error && error->message ? error->message : "no detail");
98 g_error_free (error);
99 error = NULL;
100 goto theend;
101 }
102 g_print ("Creating virtual table 'copytable' from @xml_model\n");
103 if (!gda_vconnection_data_model_add_model (GDA_VCONNECTION_DATA_MODEL (cnc), xml_model, "copytable", &error)) {
104 g_print ("Add XML model error: %s\n", error && error->message ? error->message : "no detail");
105 g_error_free (error);
106 error = NULL;
107 goto theend;
108 }
109 /*
110 if (!gda_vconnection_data_model_add_model (GDA_VCONNECTION_DATA_MODEL (cnc), csv_model, "csv", &error)) {
111 g_print ("Add model error: %s\n", error && error->message ? error->message : "no detail");
112 g_error_free (error);
113 error = NULL;
114 goto theend;
115 }
116 */
117
118 if (! test_sql_select (cnc, "SELECT * from mytable"))
119 goto theend;
120 if (! test_sql_select (cnc, "SELECT * from mytable where id=3"))
121 goto theend;
122 if (! test_sql_select (cnc, "pragma table_info ('copytable')"))
123 goto theend;
124 if (! test_sql_select (cnc, "SELECT a.*, b.* from mytable a join copytable b on (a.id!=b.id) where a.id=3"))
125 goto theend;
126 if (! test_sql_non_select (cnc, "CREATE TABLE anew (id int, string name)"))
127 goto theend;
128 if (! test_sql_non_select (cnc, "INSERT INTO anew SELECT id, name FROM copytable"))
129 goto theend;
130 if (! test_sql_select (cnc, "SELECT * from anew"))
131 goto theend;
132
133 if (! test_sql_select (cnc, "SELECT * from rwtable"))
134 goto theend;
135
136 /* INSERT test */
137 if (! test_sql_non_select (cnc, "INSERT INTO rwtable SELECT id, name FROM copytable"))
138 goto theend;
139 if (! test_sql_select (cnc, "SELECT * from rwtable order by 1"))
140 goto theend;
141
142 /* DELETE test */
143 if (! test_sql_non_select (cnc, "BEGIN"))
144 goto theend;
145 if (! test_sql_non_select (cnc, "DELETE FROM rwtable where id=3"))
146 goto theend;
147 if (! test_sql_select (cnc, "SELECT * from rwtable"))
148 goto theend;
149 if (! test_sql_non_select (cnc, "ROLLBACK"))
150 goto theend;
151
152 /* UPDATE test */
153 if (! test_sql_non_select (cnc, "BEGIN"))
154 goto theend;
155 if (! test_sql_non_select (cnc, "UPDATE rwtable set id=21 where id=3"))
156 goto theend;
157 if (! test_sql_select (cnc, "SELECT * from rwtable"))
158 goto theend;
159 if (! test_sql_non_select (cnc, "COMMIT"))
160 goto theend;
161 if (! test_sql_select (cnc, "SELECT * from rwtable"))
162 goto theend;
163
164 /* remove a table */
165 if (!gda_vconnection_data_model_remove (GDA_VCONNECTION_DATA_MODEL (cnc), gda_vconnection_data_model_get_table_name (GDA_VCONNECTION_DATA_MODEL (cnc), proxy), &error)) {
166 g_print ("Remove Proxy model error: %s\n", error && error->message ? error->message : "no detail");
167 g_error_free (error);
168 error = NULL;
169 goto theend;
170 }
171 test_sql_select (cnc, "SELECT * from mytable");
172
173 theend:
174 if (proxy)
175 g_object_unref (proxy);
176 g_object_unref (xml_model);
177 g_object_unref (cnc);
178 g_object_unref (provider);
179
180 return 0;
181 }
182
183 static gboolean
test_sql_select(GdaConnection * cnc,const gchar * sql)184 test_sql_select (GdaConnection *cnc, const gchar *sql)
185 {
186 GdaStatement *stmt;
187 GdaDataModel *data;
188 GError *error = NULL;
189
190 g_print ("\n\n============== SQL: %s\n", sql);
191 stmt = gda_sql_parser_parse_string (parser, sql, NULL, NULL);
192 g_assert (stmt);
193 data = gda_connection_statement_execute_select (cnc, stmt, NULL, &error);
194 g_object_unref (stmt);
195 if (data) {
196 gda_data_model_dump (data, stdout);
197 g_object_unref (data);
198 return TRUE;
199 }
200 else {
201 g_print ("SELECT error: %s\n", error && error->message ? error->message : "no detail");
202 g_error_free (error);
203 error = NULL;
204 return FALSE;
205 }
206 }
207
208 static gboolean
test_sql_non_select(GdaConnection * cnc,const gchar * sql)209 test_sql_non_select (GdaConnection *cnc, const gchar *sql)
210 {
211 GdaStatement *stmt;
212 GError *error = NULL;
213 gint nrows;
214
215 g_print ("\n\n============== SQL: %s\n", sql);
216 stmt = gda_sql_parser_parse_string (parser, sql, NULL, NULL);
217 g_assert (stmt);
218 nrows = gda_connection_statement_execute_non_select (cnc, stmt, NULL, NULL, &error);
219 g_object_unref (stmt);
220 if (nrows == -1) {
221 g_print ("NON SELECT error: %s\n", error && error->message ? error->message : "no detail");
222 g_error_free (error);
223 error = NULL;
224 return FALSE;
225 }
226 else {
227 gchar *msg = "";
228 if (nrows == -2)
229 msg = " (unknown number of impacted rows)";
230 g_print ("Impacted rows: %d%s\n", nrows, msg);
231 return TRUE;
232 }
233 }
234