1 /// Retrieve metadata from a DB. 2 module mysql.metadata; 3 4 import std.array; 5 import std.conv; 6 import std.datetime; 7 import std.exception; 8 9 import mysql.commands; 10 import mysql.exceptions; 11 import mysql.protocol.sockets; 12 import mysql.result; 13 14 /// A struct to hold column metadata 15 struct ColumnInfo 16 { 17 /// The database that the table having this column belongs to. 18 string schema; 19 /// The table that this column belongs to. 20 string table; 21 /// The name of the column. 22 string name; 23 /// Zero based index of the column within a table row. 24 size_t index; 25 /// Is the default value NULL? 26 bool defaultNull; 27 /// The default value as a string if not NULL 28 string defaultValue; 29 /// Can the column value be set to NULL 30 bool nullable; 31 /// What type is the column - tinyint, char, varchar, blob, date etc 32 string type; 33 /// Capacity in characters, -1L if not applicable 34 long charsMax; 35 /// Capacity in bytes - same as chars if not a unicode table definition, -1L if not applicable. 36 long octetsMax; 37 /// Presentation information for numerics, -1L if not applicable. 38 short numericPrecision; 39 /// Scale information for numerics or NULL, -1L if not applicable. 40 short numericScale; 41 /// Character set, "<NULL>" if not applicable. 42 string charSet; 43 /// Collation, "<NULL>" if not applicable. 44 string collation; 45 /// More detail about the column type, e.g. "int(10) unsigned". 46 string colType; 47 /// Information about the column's key status, blank if none. 48 string key; 49 /// Extra information. 50 string extra; 51 /// Privileges for logged in user. 52 string privileges; 53 /// Any comment that was set at table definition time. 54 string comment; 55 } 56 57 /// A struct to hold stored function metadata. 58 struct MySQLProcedure 59 { 60 string db; 61 string name; 62 string type; 63 string definer; 64 DateTime modified; 65 DateTime created; 66 string securityType; 67 string comment; 68 string charSetClient; 69 string collationConnection; 70 string collationDB; 71 } 72 73 /++ 74 Facilities to recover meta-data from a connection. 75 76 It is important to bear in mind that the methods provided will only return the 77 information that is available to the connected user. This may well be quite limited. 78 +/ 79 struct MetaData 80 { 81 import mysql.connection; 82 83 private: 84 Connection _con; 85 86 MySQLProcedure[] stored(bool procs) 87 { 88 enforce!MYX(_con.currentDB.length, "There is no selected database"); 89 string query = procs ? "SHOW PROCEDURE STATUS WHERE db='": "SHOW FUNCTION STATUS WHERE db='"; 90 query ~= _con.currentDB ~ "'"; 91 92 auto rs = _con.query(query).array; 93 MySQLProcedure[] pa; 94 pa.length = rs.length; 95 foreach (size_t i; 0..rs.length) 96 { 97 MySQLProcedure foo; 98 Row r = rs[i]; 99 foreach (int j; 0..11) 100 { 101 if (r.isNull(j)) 102 continue; 103 auto value = r[j].toString(); 104 switch (j) 105 { 106 case 0: 107 foo.db = value; 108 break; 109 case 1: 110 foo.name = value; 111 break; 112 case 2: 113 foo.type = value; 114 break; 115 case 3: 116 foo.definer = value; 117 break; 118 case 4: 119 foo.modified = r[j].get!(DateTime); 120 break; 121 case 5: 122 foo.created = r[j].get!(DateTime); 123 break; 124 case 6: 125 foo.securityType = value; 126 break; 127 case 7: 128 foo.comment = value; 129 break; 130 case 8: 131 foo.charSetClient = value; 132 break; 133 case 9: 134 foo.collationConnection = value; 135 break; 136 case 10: 137 foo.collationDB = value; 138 break; 139 default: 140 assert(0); 141 } 142 } 143 pa[i] = foo; 144 } 145 return pa; 146 } 147 148 public: 149 this(Connection con) 150 { 151 _con = con; 152 } 153 154 /++ 155 List the available databases 156 157 Note that if you have connected using the credentials of a user with 158 limited permissions you may not get many results. 159 160 Returns: 161 An array of strings 162 +/ 163 string[] databases() 164 { 165 auto rs = _con.query("SHOW DATABASES").array; 166 string[] dbNames; 167 dbNames.length = rs.length; 168 foreach (size_t i; 0..rs.length) 169 dbNames[i] = rs[i][0].toString(); 170 return dbNames; 171 } 172 173 /++ 174 List the tables in the current database 175 176 Returns: 177 An array of strings 178 +/ 179 string[] tables() 180 { 181 auto rs = _con.query("SHOW TABLES").array; 182 string[] tblNames; 183 tblNames.length = rs.length; 184 foreach (size_t i; 0..rs.length) 185 tblNames[i] = rs[i][0].toString(); 186 return tblNames; 187 } 188 189 /++ 190 Get column metadata for a table in the current database 191 192 Params: 193 table = The table name 194 Returns: 195 An array of `ColumnInfo` structs 196 +/ 197 ColumnInfo[] columns(string table) 198 { 199 // Manually specify all fields to avoid problems when newer versions of 200 // the server add or rearrange fields. (Issue #45) 201 string query = 202 "SELECT " ~ 203 " TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME," ~ 204 " COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT," ~ 205 " IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH," ~ 206 " CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE," ~ 207 " CHARACTER_SET_NAME, COLLATION_NAME, COLUMN_TYPE," ~ 208 " COLUMN_KEY, EXTRA, PRIVILEGES, COLUMN_COMMENT" ~ 209 " FROM information_schema.COLUMNS WHERE" ~ 210 " table_schema='" ~ _con.currentDB ~ "' AND table_name='" ~ table ~ "'"; 211 auto rs = _con.query(query).array; 212 ColumnInfo[] ca; 213 ca.length = rs.length; 214 foreach (size_t i; 0..rs.length) 215 { 216 ColumnInfo col; 217 Row r = rs[i]; 218 for (int j = 1; j < 19; j++) 219 { 220 string t; 221 bool isNull = r.isNull(j); 222 if (!isNull) 223 t = to!string(r[j]); 224 switch (j) 225 { 226 case 1: 227 col.schema = t; 228 break; 229 case 2: 230 col.table = t; 231 break; 232 case 3: 233 col.name = t; 234 break; 235 case 4: 236 if(isNull) 237 col.index = -1; 238 else 239 col.index = cast(size_t)(r[j].coerce!ulong() - 1); 240 //col.index = cast(size_t)(r[j].get!(ulong)-1); 241 break; 242 case 5: 243 if (isNull) 244 col.defaultNull = true; 245 else 246 col.defaultValue = t; 247 break; 248 case 6: 249 if (t == "YES") 250 col.nullable = true; 251 break; 252 case 7: 253 col.type = t; 254 break; 255 case 8: 256 col.charsMax = cast(long)(isNull? -1L: r[j].coerce!(ulong)); 257 break; 258 case 9: 259 col.octetsMax = cast(long)(isNull? -1L: r[j].coerce!(ulong)); 260 break; 261 case 10: 262 col.numericPrecision = cast(short) (isNull? -1: r[j].coerce!(ulong)); 263 break; 264 case 11: 265 col.numericScale = cast(short) (isNull? -1: r[j].coerce!(ulong)); 266 break; 267 case 12: 268 col.charSet = isNull? "<NULL>": t; 269 break; 270 case 13: 271 col.collation = isNull? "<NULL>": t; 272 break; 273 case 14: 274 col.colType = r[j].get!string(); 275 break; 276 case 15: 277 col.key = t; 278 break; 279 case 16: 280 col.extra = t; 281 break; 282 case 17: 283 col.privileges = t; 284 break; 285 case 18: 286 col.comment = t; 287 break; 288 default: 289 break; 290 } 291 } 292 ca[i] = col; 293 } 294 return ca; 295 } 296 297 /// Get list of stored functions in the current database, and their properties. 298 MySQLProcedure[] functions() 299 { 300 return stored(false); 301 } 302 303 /// Get list of stored procedures in the current database, and their properties. 304 MySQLProcedure[] procedures() 305 { 306 return stored(true); 307 } 308 }