git.roojs.org Git - app.Builder.js/blob - src/Palete/RooDatabase.vala

   1 /*
   2  * Renderer for Roo Database code
   3  *
   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
   7  *
   8  */
   9
  10  // should this be in palete...
  11 namespace Palete {
  12
  13
  14     public class RooDatabase : Object
  15     {
  16         public Project.Project project;
  17
  18                 public string DBTYPE;
  19                 public string DBNAME;
  20
  21         public Gda.Connection cnc;
  22
  23                 public RooDatabase.from_project (Project.Project project)
  24         {
  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) {
  29                                 return;
  30                         }
  31
  32
  33                         try {
  34
  35                                         this.cnc = Gda.Connection.open_from_string (
  36                                         this.DBTYPE,
  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
  41                                 );
  42                         } catch(Gda.ConfigError e) {
  43                                 GLib.warning("%s\n", e.message);
  44                                 this.cnc  = null;
  45                                 this.DBTYPE = "";
  46                         } catch(Gda.ConnectionError e) {
  47                                 GLib.warning("%s\n", e.message);
  48                                 this.cnc  = null;
  49                                 this.DBTYPE = "";
  50                         }
  51
  52         }
  53
  54                 public RooDatabase.from_cfg (string dbtype, string dbname, string dbuser, string dbpass)
  55                 {
  56                         this.DBTYPE = dbtype;
  57                         this.DBNAME = dbname;
  58                         try {
  59                                  this.cnc = Gda.Connection.open_from_string (
  60                                         this.DBTYPE,
  61                                         "DB_NAME=" + dbname,
  62                                         "USERNAME=" + dbuser +
  63                                         ";PASSWORD=" + dbpass,
  64                                         Gda.ConnectionOptions.NONE
  65                                 );
  66                         } catch(Gda.ConfigError e) {
  67                                 this.cnc  = null;
  68                                 this.DBTYPE = "";
  69                         } catch(Gda.ConnectionError e) {
  70                                 this.cnc  = null;
  71                                 this.DBTYPE = "";
  72                         }
  73
  74
  75         }
  76
  77
  78         public Json.Array readTables()
  79         {
  80                         try {
  81                                 if (this.DBTYPE == "PostgreSQL") {
  82
  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)
  88                                                 """));
  89
  90                                 }
  91                                 if (this.DBTYPE == "MySQL") {
  92                                         return this.fetchAll(this.cnc.execute_select_command( "SHOW TABLES" ));
  93                                 }
  94                         } catch (GLib.Error e) {
  95                         }
  96                         GLib.warning("Read tables failed DBTYPE = %s\n", this.DBTYPE);
  97                         return new Json.Array();
  98
  99                 }
 100                 public Gee.ArrayList readTablesGee()
 101                 {
 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));
 106                         }
 107                         return ret;
 108
 109                 }
 110
 111                 public Json.Object readTable(string tablename)
 112                 {
 113
 114                         Json.Array res_ar = new Json.Array();
 115                         var res = new Json.Object();
 116                         try {
 117                                 switch (this.DBTYPE ) {
 118                                         case "PostgreSQL":
 119
 120                                                 res_ar =   this.fetchAll(this.cnc.execute_select_command(
 121                                                 """
 122
 123                                                  SELECT
 124                                                         f.attnum AS number,
 125                                                         f.attname AS Field,
 126                                                         f.attnum,
 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;
 144
 145                                                 """));
 146                                                 break;
 147
 148                                         case  "MySQL":
 149                                                 res_ar = this.fetchAll(this.cnc.execute_select_command( "DESCRIBE " + tablename ));
 150                                                 break;
 151
 152                                         default:
 153                                                 return res;
 154
 155                                 }
 156                          } catch (GLib.Error e) {
 157                                  return res;
 158                          }
 159
 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);
 163                         }
 164                         return res;
 165
 166
 167                 }
 168
 169
 170
 171
 172
 173                 public Json.Object readForeignKeys(string table)
 174         {
 175
 176
 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") {
 180                                 return  ret;
 181                         }
 182
 183                         var query = """
 184                                 SELECT
 185                                 TABLE_COMMENT
 186                                 FROM
 187                                 information_schema.TABLES
 188                                 WHERE
 189                                 TABLE_NAME = '""" + table + """'
 190                                 AND
 191                                 TABLE_SCHEMA = '""" + this.DBNAME + """'
 192                         """;
 193
 194                         var jarr = new Json.Array();
 195                         try {
 196                            jarr = this.fetchAll(this.cnc.execute_select_command(
 197                                                 query
 198                                 ));
 199                                 if (jarr.get_length() < 1) {
 200                                         return  ret;
 201                                 }
 202                         } catch (GLib.Error e) {
 203                                 return ret;
 204                         }
 205                         var contents = jarr.get_string_element(0);
 206                         GLib.debug(contents);
 207                         if (contents == null) {
 208                                 return ret;
 209                         }
 210
 211                          GLib.Regex exp = /FK\(([^\)]+)\)/;
 212                          string str = "";
 213
 214                         GLib.MatchInfo mi;
 215                         if ( exp.match (contents, 0, out mi) ) {
 216
 217                                 str = mi.fetch(1);
 218                                 GLib.debug("match = %s", str);
 219                         }
 220
 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())) {
 225                                         continue;
 226                                 }
 227                                 var o = ret.get_object_member(kv[0].strip());
 228
 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);
 235
 236
 237                         }
 238                         return ret;
 239
 240                 }
 241         public Json.Array fetchAll(Gda.DataModel qnr)
 242                 {
 243                         var cols = new Gee.ArrayList<string>();
 244
 245                         for (var i =0;i < qnr.get_n_columns(); i++) {
 246                                 cols.add(qnr.get_column_name(i));
 247                         }
 248                         //print(Json.stringify(cols, null,4));
 249
 250                         var res = new Json.Array();
 251                          //print("ROWS %d\n", qnr.get_n_rows());
 252
 253                         for (var r = 0; r < qnr.get_n_rows(); r++) {
 254
 255                                 // single clo..
 256                                 //print("GOT ROW");
 257                                 //print("COLS  %d\n", cols.size);
 258                                 if (cols.size == 1) {
 259
 260                                         //print("GOT %s\n",str);
 261                                         try {
 262                                                 res.add_string_element(qnr.get_value_at(0,r).get_string());
 263                                         } catch (GLib.Error e) {
 264                                                 res.add_string_element("");
 265                                         }
 266                                         continue;
 267                                 }
 268
 269                                 var add = new Json.Object();
 270
 271                                 for (var i = 0; i < cols.size; i++) {
 272                                         var n = cols.get(i);
 273                                         try {
 274                                                 var val = qnr.get_value_at(i,r);
 275                                                 var type = val.type().name();
 276                                                 //print("%s\n",type);
 277                                                 switch(type) {
 278                                                         case "GdaBinary":
 279                                                         case "GdaBlob":
 280                                                                 add.set_string_member(n, "?? big string ??");
 281                                                                 break;
 282
 283                                                         case  "GdaNull":
 284                                                                 add.set_null_member(n);
 285                                                                 break;
 286
 287                                                         default:
 288                                                                 add.set_string_member(n, val.get_string());
 289                                                                 break;
 290                                                 }
 291                                         } catch (GLib.Error e ) {
 292                                                 add.set_string_member(n, "");
 293                                         }
 294
 295                                 }
 296
 297                                 res.add_object_element(add);
 298
 299                         }
 300                         return res;
 301
 302                 }
 303
 304
 305
 306         }
 307
 308
 309 }
 310 // valac --pkg libgda-5.0 --pkg gee-1.0 --pkg json-glib-1.0  --pkg libxml-2.0   RooDatabase.vala  -o /tmp/rdtest
 311 /*
 312  void main() {
 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");
 317
 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);
 322
 323             generator.pretty = true;
 324             generator.indent = 4;
 325
 326
 327     print("%s\n", generator.to_data (null));
 328  }
 329 */