git.roojs.org Git - app.Builder.js/blob - src/JsRender/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 JsRender {
  12
  13
  14     class RooDatabase : Object
  15     {
  16         public Project.Project project;
  17
  18         public string DBTYPE;
  19
  20         public Gda.Connection cnc;
  21
  22                 public RooDatabase (Project.Project project)
  23         {
  24             this.project = project;
  25             this.DBTYPE = this.project.json_project_data.get_string_member("DBTYPE");
  26             this.cnc = Gda.Connection.open_from_string (
  27                                 this.DBTYPE,
  28                                 "DB_NAME=" + this.project.json_project_data.get_string_member("DBNAME"),
  29                                 "USERNAME=" + this.project.json_project_data.get_string_member("DBUSERNAME") +
  30                                 ";PASSWORD=" + this.project.json_project_data.get_string_member("DBPASSWORD"),
  31                                 Gda.ConnectionOptions.NONE
  32                         );
  33
  34         }
  35
  36
  37
  38         public Json.Array readTables()
  39         {
  40
  41                         if (this.DBTYPE == "PostgreSQL") {
  42
  43                                 return this.fetchAll(this.cnc.execute_select_command(
  44                                         """select c.relname FROM pg_catalog.pg_class c
  45                                                 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
  46                                                 WHERE c.relkind IN ('r','') AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
  47                                                 AND pg_catalog.pg_table_is_visible(c.oid)
  48                                         """));
  49
  50                         }
  51                         if (this.DBTYPE == "MySQL") {
  52                                 return this.fetchAll(this.cnc.execute_select_command( "SHOW TABLES" ));
  53                         }
  54                         return new Json.Array();
  55
  56                 }
  57
  58                 public Json.Array readTable(string tablename) {
  59                         if (this.DBTYPE== "PostgreSQL") {
  60
  61                                 return this.fetchAll(this.cnc.execute_select_command(
  62                                         """
  63
  64                                          SELECT
  65                                                 f.attnum AS number,
  66                                                 f.attname AS Field,
  67                                                 f.attnum,
  68                                                 CASE WHEN f.attnotnull = 't' THEN 'NO' ELSE 'YES' END AS isNull,
  69                                                 pg_catalog.format_type(f.atttypid,f.atttypmod) AS Type,
  70                                                 CASE WHEN p.contype = 'p' THEN 't' ELSE 'f' END AS primarykey,
  71                                                 CASE WHEN p.contype = 'u' THEN 't' ELSE 'f' END AS uniquekey,
  72                                                 CASE WHEN p.contype = 'f' THEN g.relname END AS foreignkey,
  73                                                 CASE WHEN p.contype = 'f' THEN p.confkey END AS foreignkey_fieldnum,
  74                                                 CASE WHEN p.contype = 'f' THEN g.relname END AS foreignkey,
  75                                                 CASE WHEN p.contype = 'f' THEN p.conkey END AS foreignkey_connnum,
  76                                                 CASE WHEN f.atthasdef = 't' THEN d.adsrc END AS default
  77                                                 FROM pg_attribute f JOIN pg_class c ON c.oid = f.attrelid
  78                                                                 JOIN pg_type t ON t.oid = f.atttypid
  79                                                                 LEFT JOIN pg_attrdef d ON d.adrelid = c.oid AND d.adnum = f.attnum
  80                                                                 LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
  81                                                                 LEFT JOIN pg_constraint p ON p.conrelid = c.oid AND f.attnum = ANY ( p.conkey )
  82                                                                 LEFT JOIN pg_class AS g ON p.confrelid = g.oid
  83                                                 WHERE c.relkind = 'r'::char AND n.nspname = 'public'
  84                                                 AND c.relname = '""" + tablename + """' AND f.attnum > 0 ORDER BY number;
  85
  86                                         """));
  87
  88                         }
  89                         if (this.DBTYPE== "MySQL") {
  90                                 return this.fetchAll(this.cnc.execute_select_command( "DESCRIBE " + tablename ));
  91                         }
  92                         return  new Json.Array();
  93
  94
  95
  96                 }
  97
  98                 public Json.Array readForeignKeys(string table)
  99         {
 100                         var ret =   new Json.Array();
 101                         // technically we should use FK stuff in mysql, but for the momemnt use my hacky FK()
 102                         if (this.DBTYPE != "MySQL") {
 103                                 return  ret;
 104                         }
 105
 106                         var jarr = this.fetchAll(this.cnc.execute_select_command(
 107                                         """
 108
 109                                          SELECT
 110                                                 TABLE_COMMENT
 111                                                 FROM
 112                                                 information_schema.TABLES
 113                                                 WHERE
 114                                                 TABLE_NAME = '""" + table + """'
 115                                                 AND
 116                                                 TABLE_SCHEMA = '""" + this.project.json_project_data.get_string_member("DBNAME") + """'
 117                                         """
 118                                         ));
 119
 120                         if (jarr.get_length() < 1) {
 121                                 return  ret;
 122                         }
 123                         var contents = jarr.get_string_element(0);
 124
 125                          GLib.Regex exp = /FK\(([^\)]+)\)/;
 126                          string str = "";
 127                          try {
 128                                 GLib.MatchInfo mi;
 129                                 if ( exp.match (contents, 0, out mi) ) {
 130                                         mi.next();
 131                                         str = mi.fetch(0);
 132                                 }
 133                         } catch (GLib.Error e) {
 134                                 return  ret;
 135                         }
 136                         var ar = str.split("\n");
 137                         for (var i = 0; i < ar.length; i++) {
 138                                 var kv = ar[i].split("=");
 139                                 var o = new Json.Object();
 140                                 o.set_string_member("key", kv[0].strip());
 141                                 var lr = kv[1].split(":");
 142                                 o.set_string_member("table", lr[0].strip());
 143                                 o.set_string_member("col", lr[1].strip());
 144                                 ret.add_object_element(o);
 145                         }
 146                         return ret;
 147
 148
 149
 150                 }
 151         public Json.Array fetchAll(Gda.DataModel qnr)
 152                 {
 153                         var cols = new Gee.ArrayList<string>();
 154
 155                         for (var i =0;i < qnr.get_n_columns(); i++) {
 156                                 cols.add(qnr.get_column_name(i));
 157                         }
 158                         //print(Json.stringify(cols, null,4));
 159                         var iter = qnr.create_iter();
 160                         var res = new Json.Array();
 161                         //print(this.get_n_rows());
 162
 163                         for (var r = 0; r < qnr.get_n_rows(); r++) {
 164
 165                                 // single clo..
 166                                 //print("GOT ROW");
 167                                 if (cols.size == 1) {
 168                                         res.add_string_element(qnr.get_value_at(0,r).get_string());
 169                                         continue;
 170                                 }
 171
 172                                 var add = new Json.Object();
 173
 174                                 for (var i = 0; i < cols.size; i++) {
 175                                         var n = cols.get(i);
 176                                         var val = qnr.get_value_at(i,r);
 177                                         var type = val.type().name();
 178                                         if (type == "GdaBinary" || type == "GdaBlob") {
 179                                                 add.set_string_member(n, val.value.to_string(1024));
 180                                                 continue;
 181                                         }
 182                                         add.set_string_member(n, val.get_string());
 183
 184                                 }
 185
 186                                 res.add_object_element(add);
 187
 188                         }
 189                         return res;
 190
 191                 }
 192
 193                 public void test()
 194                 {
 195
 196                 }
 197
 198
 199         }
 200
 201
 202 }
 203
 204
 205