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    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                         try {
  29
  30                                         this.cnc = Gda.Connection.open_from_string (
  31                                         this.DBTYPE,
  32                                         "DB_NAME=" + this.DBNAME,
  33                                         "USERNAME=" + this.project.get_string_member("DBUSERNAME") +
  34                                         ";PASSWORD=" + this.project.get_string_member("DBPASSWORD"),
  35                                         Gda.ConnectionOptions.NONE
  36                                 );
  37                         } catch(Gda.ConfigError e) {
  38                                 this.cnc  = null;
  39                                 this.DBTYPE = "";
  40                         }
  41
  42         }
  43
  44         public RooDatabase.from_cfg (string dbtype, string dbname, string dbuser, string dbpass)
  45          {
  46              this.DBTYPE = dbtype;
  47                         this.DBNAME = dbname;
  48
  49              this.cnc = Gda.Connection.open_from_string (
  50                                 this.DBTYPE,
  51                                 "DB_NAME=" + dbname,
  52                                 "USERNAME=" + dbuser +
  53                                 ";PASSWORD=" + dbpass,
  54                                 Gda.ConnectionOptions.NONE
  55                         );
  56
  57
  58
  59         }
  60
  61
  62         public Json.Array readTables()
  63         {
  64
  65                         if (this.DBTYPE == "PostgreSQL") {
  66
  67                                 return this.fetchAll(this.cnc.execute_select_command(
  68                                         """select c.relname FROM pg_catalog.pg_class c
  69                                                 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
  70                                                 WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
  71                                                 AND pg_catalog.pg_table_is_visible(c.oid)
  72                                         """));
  73
  74                         }
  75                         if (this.DBTYPE == "MySQL") {
  76                                 return this.fetchAll(this.cnc.execute_select_command( "SHOW TABLES" ));
  77                         }
  78                         return new Json.Array();
  79
  80                 }
  81                 public Gee.ArrayList readTablesGee()
  82                 {
  83                         var ret = new Gee.ArrayList<string>();
  84                         var ar = this.readTables();
  85                         for(var i = 0; i < ar.get_length(); i++) {
  86                                 ret.add(ar.get_string_element(i));
  87                         }
  88                         return ret;
  89
  90                 }
  91
  92                 public Json.Object readTable(string tablename)
  93                 {
  94
  95                         Json.Array res_ar;
  96                         var res = new Json.Object();
  97
  98                         switch (this.DBTYPE ) {
  99                                 case "PostgreSQL":
 100
 101                                         res_ar =   this.fetchAll(this.cnc.execute_select_command(
 102                                         """
 103
 104                                          SELECT
 105                                                 f.attnum AS number,
 106                                                 f.attname AS Field,
 107                                                 f.attnum,
 108                                                 CASE WHEN f.attnotnull = 't' THEN 'NO' ELSE 'YES' END AS isNull,
 109                                                 pg_catalog.format_type(f.atttypid,f.atttypmod) AS Type,
 110                                                 CASE WHEN p.contype = 'p' THEN 't' ELSE 'f' END AS primarykey,
 111                                                 CASE WHEN p.contype = 'u' THEN 't' ELSE 'f' END AS uniquekey,
 112                                                 CASE WHEN p.contype = 'f' THEN g.relname END AS foreignkey,
 113                                                 CASE WHEN p.contype = 'f' THEN p.confkey END AS foreignkey_fieldnum,
 114                                                 CASE WHEN p.contype = 'f' THEN g.relname END AS foreignkey,
 115                                                 CASE WHEN p.contype = 'f' THEN p.conkey END AS foreignkey_connnum,
 116                                                 CASE WHEN f.atthasdef = 't' THEN d.adsrc END AS default
 117                                                 FROM pg_attribute f JOIN pg_class c ON c.oid = f.attrelid
 118                                                                 JOIN pg_type t ON t.oid = f.atttypid
 119                                                                 LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum
 120                                                                 LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
 121                                                                 LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY ( p.conkey )
 122                                                                 LEFT JOIN pg_class AS g ON p.confrelid = g.oid
 123                                                 WHERE c.relkind = 'r'::char AND n.nspname = 'public'
 124                                                 AND c.relname = '""" + tablename + """' AND f.attnum > 0 ORDER BY number;
 125
 126                                         """));
 127                                         break;
 128
 129                                 case  "MySQL":
 130                                         res_ar = this.fetchAll(this.cnc.execute_select_command( "DESCRIBE " + tablename ));
 131                                         break;
 132
 133                                 default:
 134                                         return res;
 135                                         break;
 136                         }
 137
 138                         for (var i =0; i < res_ar.get_length(); i++) {
 139                                 var el = res_ar.get_object_element(i);
 140                                 res.set_object_member( el.get_string_member("Field"), el);
 141                         }
 142                         return res;
 143
 144
 145                 }
 146
 147
 148
 149
 150
 151                 public Json.Object readForeignKeys(string table)
 152         {
 153
 154
 155                         var ret =   this.readTable(table);
 156                         // technically we should use FK stuff in mysql, but for the momemnt use my hacky FK()
 157                         if (this.DBTYPE != "MySQL") {
 158                                 return  ret;
 159                         }
 160
 161                         var query = """
 162                                 SELECT
 163                                 TABLE_COMMENT
 164                                 FROM
 165                                 information_schema.TABLES
 166                                 WHERE
 167                                 TABLE_NAME = '""" + table + """'
 168                                 AND
 169                                 TABLE_SCHEMA = '""" + this.DBNAME + """'
 170                         """;
 171
 172                         var jarr = this.fetchAll(this.cnc.execute_select_command(
 173                                         query
 174                                         ));
 175                         if (jarr.get_length() < 1) {
 176                                 return  ret;
 177                         }
 178
 179                         var contents = jarr.get_string_element(0);
 180
 181                          GLib.Regex exp = /FK\(([^\)]+)\)/;
 182                          string str = "";
 183                          try {
 184                                 GLib.MatchInfo mi;
 185                                 if ( exp.match (contents, 0, out mi) ) {
 186
 187                                         str = mi.fetch(1);
 188                                         print("match = %s", str);
 189                                 }
 190                         } catch (GLib.Error e) {
 191                                 return  ret;
 192                         }
 193                         var ar = str.split("\n");
 194                         for (var i = 0; i < ar.length; i++) {
 195                                 var kv = ar[i].split("=");
 196                                 if (!ret.has_member(kv[0].strip())) {
 197                                         continue;
 198                                 }
 199                                 var o = ret.get_object_member(kv[0].strip());
 200
 201                                 //o.set_string_member("key", kv[0].strip());
 202                                 var lr = kv[1].split(":");
 203                                 o.set_string_member("relates_to_table", lr[0].strip());
 204                                 o.set_string_member("relates_to_col", lr[1].strip());
 205                                 o.set_object_member("relates_to_schema", this.readTable(lr[0].strip()));
 206                                 //ret.set_object_member(kv[0].strip(),o);
 207
 208
 209                         }
 210                         return ret;
 211
 212                 }
 213         public Json.Array fetchAll(Gda.DataModel qnr)
 214                 {
 215                         var cols = new Gee.ArrayList<string>();
 216
 217                         for (var i =0;i < qnr.get_n_columns(); i++) {
 218                                 cols.add(qnr.get_column_name(i));
 219                         }
 220                         //print(Json.stringify(cols, null,4));
 221                         var iter = qnr.create_iter();
 222                         var res = new Json.Array();
 223                          //print("ROWS %d\n", qnr.get_n_rows());
 224
 225                         for (var r = 0; r < qnr.get_n_rows(); r++) {
 226
 227                                 // single clo..
 228                                 //print("GOT ROW");
 229                                 //print("COLS  %d\n", cols.size);
 230                                 if (cols.size == 1) {
 231                                         var str = qnr.get_value_at(0,r).get_string();
 232                                         //print("GOT %s\n",str);
 233                                         res.add_string_element(qnr.get_value_at(0,r).get_string());
 234                                         continue;
 235                                 }
 236
 237                                 var add = new Json.Object();
 238
 239                                 for (var i = 0; i < cols.size; i++) {
 240                                         var n = cols.get(i);
 241                                         var val = qnr.get_value_at(i,r);
 242                                         var type = val.type().name();
 243                                         //print("%s\n",type);
 244                                         switch(type) {
 245                                                 case "GdaBinary":
 246                                                 case "GdaBlob":
 247                                                         add.set_string_member(n, "?? big string ??");
 248                                                         break;
 249
 250                                                 case  "GdaNull":
 251                                                         add.set_null_member(n);
 252                                                         break;
 253
 254                                                 default:
 255                                                         add.set_string_member(n, val.get_string());
 256                                                         break;
 257                                         }
 258
 259                                 }
 260
 261                                 res.add_object_element(add);
 262
 263                         }
 264                         return res;
 265
 266                 }
 267
 268
 269
 270         }
 271
 272
 273 }
 274 // valac --pkg libgda-5.0 --pkg gee-1.0 --pkg json-glib-1.0  --pkg libxml-2.0   RooDatabase.vala  -o /tmp/rdtest
 275 /*
 276  void main() {
 277      var x = new JsRender.RooDatabase.from_cfg("MySQL", "hydra", "root", "");
 278     // var res = x.readTables();
 279     //var res= x.readTable("Person");
 280     var res= x.readForeignKeys("Person");
 281
 282         var  generator = new Json.Generator ();
 283     var  root = new Json.Node(Json.NodeType.OBJECT);
 284     root.init_object(res);
 285     generator.set_root (root);
 286
 287             generator.pretty = true;
 288             generator.indent = 4;
 289
 290
 291     print("%s\n", generator.to_data (null));
 292  }
 293 */