2 * Renderer for Roo Database code
4 * - Concept - this code does the SQL queries
5 * pulls data from information_schema (on mysql only at present..)
6 * Sends it down the line to the Javascript code. to generate structures
10 // should this be in palete...
14 public class RooDatabase : Object
16 public Project.Project project;
21 public Gda.Connection cnc;
23 public RooDatabase.from_project (Project.Project project)
25 this.project = project;
26 this.DBTYPE = this.project.get_string_member("DBTYPE");
27 this.DBNAME = this.project.get_string_member("DBNAME");
28 if (this.DBTYPE.length < 1) {
35 this.cnc = Gda.Connection.open_from_string (
37 "DB_NAME=" + this.DBNAME,
38 "USERNAME=" + this.project.get_string_member("DBUSERNAME") +
39 ";PASSWORD=" + this.project.get_string_member("DBPASSWORD"),
40 Gda.ConnectionOptions.NONE
42 } catch(Gda.ConfigError e) {
43 GLib.warning("%s\n", e.message);
46 } catch(Gda.ConnectionError e) {
47 GLib.warning("%s\n", e.message);
54 public RooDatabase.from_cfg (string dbtype, string dbname, string dbuser, string dbpass)
59 this.cnc = Gda.Connection.open_from_string (
62 "USERNAME=" + dbuser +
63 ";PASSWORD=" + dbpass,
64 Gda.ConnectionOptions.NONE
66 } catch(Gda.ConfigError e) {
69 } catch(Gda.ConnectionError e) {
78 public Json.Array readTables()
81 if (this.DBTYPE == "PostgreSQL") {
83 return this.fetchAll(this.cnc.execute_select_command(
84 """select c.relname FROM pg_catalog.pg_class c
85 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
86 WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
87 AND pg_catalog.pg_table_is_visible(c.oid)
91 if (this.DBTYPE == "MySQL") {
92 return this.fetchAll(this.cnc.execute_select_command( "SHOW TABLES" ));
94 } catch (GLib.Error e) {
96 GLib.warning("Read tables failed DBTYPE = %s\n", this.DBTYPE);
97 return new Json.Array();
100 public Gee.ArrayList readTablesGee()
102 var ret = new Gee.ArrayList<string>();
103 var ar = this.readTables();
104 for(var i = 0; i < ar.get_length(); i++) {
105 ret.add(ar.get_string_element(i));
111 public Json.Object readTable(string tablename)
114 Json.Array res_ar = new Json.Array();
115 var res = new Json.Object();
117 switch (this.DBTYPE ) {
120 res_ar = this.fetchAll(this.cnc.execute_select_command(
127 CASE WHEN f.attnotnull = 't' THEN 'NO' ELSE 'YES' END AS isNull,
128 pg_catalog.format_type(f.atttypid,f.atttypmod) AS Type,
129 CASE WHEN p.contype = 'p' THEN 't' ELSE 'f' END AS primarykey,
130 CASE WHEN p.contype = 'u' THEN 't' ELSE 'f' END AS uniquekey,
131 CASE WHEN p.contype = 'f' THEN g.relname END AS foreignkey,
132 CASE WHEN p.contype = 'f' THEN p.confkey END AS foreignkey_fieldnum,
133 CASE WHEN p.contype = 'f' THEN g.relname END AS foreignkey,
134 CASE WHEN p.contype = 'f' THEN p.conkey END AS foreignkey_connnum,
135 CASE WHEN f.atthasdef = 't' THEN d.adsrc END AS default
136 FROM pg_attribute f JOIN pg_class c ON c.oid = f.attrelid
137 JOIN pg_type t ON t.oid = f.atttypid
138 LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum
139 LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
140 LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY ( p.conkey )
141 LEFT JOIN pg_class AS g ON p.confrelid = g.oid
142 WHERE c.relkind = 'r'::char AND n.nspname = 'public'
143 AND c.relname = '""" + tablename + """' AND f.attnum > 0 ORDER BY number;
149 res_ar = this.fetchAll(this.cnc.execute_select_command( "DESCRIBE " + tablename ));
156 } catch (GLib.Error e) {
160 for (var i =0; i < res_ar.get_length(); i++) {
161 var el = res_ar.get_object_element(i);
162 res.set_object_member( el.get_string_member("Field"), el);
173 public Json.Object readForeignKeys(string table)
177 var ret = this.readTable(table);
178 // technically we should use FK stuff in mysql, but for the momemnt use my hacky FK()
179 if (this.DBTYPE != "MySQL") {
187 information_schema.TABLES
189 TABLE_NAME = '""" + table + """'
191 TABLE_SCHEMA = '""" + this.DBNAME + """'
194 var jarr = new Json.Array();
196 jarr = this.fetchAll(this.cnc.execute_select_command(
199 if (jarr.get_length() < 1) {
202 } catch (GLib.Error e) {
205 var contents = jarr.get_string_element(0);
206 GLib.debug(contents);
207 if (contents == null) {
211 GLib.Regex exp = /FK\(([^\)]+)\)/;
215 if ( exp.match (contents, 0, out mi) ) {
218 GLib.debug("match = %s", str);
221 var ar = str.split("\n");
222 for (var i = 0; i < ar.length; i++) {
223 var kv = ar[i].split("=");
224 if (!ret.has_member(kv[0].strip())) {
227 var o = ret.get_object_member(kv[0].strip());
229 //o.set_string_member("key", kv[0].strip());
230 var lr = kv[1].split(":");
231 o.set_string_member("relates_to_table", lr[0].strip());
232 o.set_string_member("relates_to_col", lr[1].strip());
233 o.set_object_member("relates_to_schema", this.readTable(lr[0].strip()));
234 //ret.set_object_member(kv[0].strip(),o);
241 public Json.Array fetchAll(Gda.DataModel qnr)
243 var cols = new Gee.ArrayList<string>();
245 for (var i =0;i < qnr.get_n_columns(); i++) {
246 cols.add(qnr.get_column_name(i));
248 //print(Json.stringify(cols, null,4));
250 var res = new Json.Array();
251 //print("ROWS %d\n", qnr.get_n_rows());
253 for (var r = 0; r < qnr.get_n_rows(); r++) {
257 //print("COLS %d\n", cols.size);
258 if (cols.size == 1) {
260 //print("GOT %s\n",str);
262 res.add_string_element(qnr.get_value_at(0,r).get_string());
263 } catch (GLib.Error e) {
264 res.add_string_element("");
269 var add = new Json.Object();
271 for (var i = 0; i < cols.size; i++) {
274 var val = qnr.get_value_at(i,r);
275 var type = val.type().name();
276 //print("%s\n",type);
280 add.set_string_member(n, "?? big string ??");
284 add.set_null_member(n);
288 add.set_string_member(n, val.get_string());
291 } catch (GLib.Error e ) {
292 add.set_string_member(n, "");
297 res.add_object_element(add);
310 // valac --pkg libgda-5.0 --pkg gee-1.0 --pkg json-glib-1.0 --pkg libxml-2.0 RooDatabase.vala -o /tmp/rdtest
313 var x = new JsRender.RooDatabase.from_cfg("MySQL", "hydra", "root", "");
314 // var res = x.readTables();
315 //var res= x.readTable("Person");
316 var res= x.readForeignKeys("Person");
318 var generator = new Json.Generator ();
319 var root = new Json.Node(Json.NodeType.OBJECT);
320 root.init_object(res);
321 generator.set_root (root);
323 generator.pretty = true;
324 generator.indent = 4;
327 print("%s\n", generator.to_data (null));