001package com.randomnoun.common.db.dao; 002 003import java.sql.ResultSet; 004import java.sql.SQLException; 005import java.util.List; 006 007import javax.sql.DataSource; 008 009import org.apache.log4j.Logger; 010import org.springframework.dao.DataAccessException; 011import org.springframework.jdbc.core.ResultSetExtractor; 012 013import com.randomnoun.common.db.DatabaseReader; 014import com.randomnoun.common.db.enums.ConstraintTypeEnum; 015import com.randomnoun.common.db.enums.DatabaseTypeEnum; 016import com.randomnoun.common.db.to.ConstraintColumnTO; 017import com.randomnoun.common.db.to.ConstraintTO; 018import com.randomnoun.common.db.to.MysqlTableColumnTO; 019import com.randomnoun.common.db.to.RoutineTO; 020import com.randomnoun.common.db.to.SchemaTO; 021import com.randomnoun.common.db.to.TableTO; 022import com.randomnoun.common.db.to.TriggerTO; 023import com.randomnoun.common.spring.StringRowMapper; 024 025//all the stuff that's specific to mysql should go in here 026public class MysqlDatabaseReader extends DatabaseReader { 027 028 Logger logger = Logger.getLogger(MysqlDatabaseReader.class); 029 030 public MysqlDatabaseReader(DataSource dataSource) { 031 super(dataSource); 032 this.db.setDbType(DatabaseTypeEnum.MYSQL); 033 } 034 035 @Override 036 public SchemaTO readSchema(String schemaName) { 037 SchemaTO schema = new SchemaTO(db, schemaName); 038 // TODO Auto-generated method stub 039 List<String> tableList = null; 040 041 // this became outrageously slow in mysql 8 042 tableList = jt.query( 043 "SELECT table_name "+ 044 " FROM information_schema.tables " + 045 " WHERE table_schema='" + schema.getName() + "'", 046 new StringRowMapper() ); 047 //tableList = schema.database.upper(tableList); 048 for (String n : tableList) { 049 logger.debug("Table " + n); 050 // TableTO t = readTable(schema, n); 051 TableTO t = new TableTO(schema, n); 052 schema.getTableMap().put(n, t); 053 } 054 055 readTables(schema); 056 readTriggers(schema); 057 readRoutines(schema); 058 return schema; 059 } 060 061 private void readTriggers(final SchemaTO s) { 062 // final TriggerTO t = new TriggerTO(schema, triggerName); 063 // return t; 064 065 jt.query( 066 "SELECT T.trigger_catalog, T.trigger_schema, T.trigger_name,\n" + 067 " T.event_manipulation,\n" + 068 " T.event_object_catalog, T.event_object_schema, T.event_object_table,\n" + 069 " T.action_order,\n" + 070 " T.action_condition,\n" + // null 071 " T.action_statement,\n" + 072 " T.action_orientation,\n" + // ROW 073 " T.action_timing,\n" + // AFTER 074 " T.action_reference_old_table, T.action_reference_new_table,\n" + // null, null 075 " T.action_reference_old_row, T.action_reference_new_row,\n" + // OLD, NEW 076 " T.created,\n" + 077 " T.sql_mode,\n" + // IGNORE_SPACE,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION 078 " T.definer,\n" + // root@localhost 079 " T.character_set_client,\n" + // utf8mb4 080 " T.collation_connection,\n" + // utf8mb4_unicode_ci 081 " T.database_collation \n" + // utf8mb4_0900_ai_ci 082 "FROM INFORMATION_SCHEMA.TRIGGERS T\n" + 083 "WHERE T.trigger_schema = '" + s.getName() + "'\n" + // TODO escape 084 " ORDER BY T.event_object_schema, T.event_object_table, T.event_manipulation, T.action_order", 085 new ResultSetExtractor<Object>() { 086 @Override 087 public Object extractData(ResultSet rs) throws SQLException, DataAccessException { 088 TableTO t = null; 089 String lastTableName = null; 090 091 while (rs.next()) { 092 String tableName = rs.getString("event_object_table"); 093 if (t == null || !tableName.equals(lastTableName)) { 094 t = s.getTable(tableName); 095 lastTableName = tableName; 096 } 097 098 String triggerName = rs.getString("trigger_name"); 099 TriggerTO trigger = new TriggerTO(t, t.getSchema().getDatabase().upper(triggerName)); 100 trigger.setEventManipulation(rs.getString("event_manipulation")); 101 trigger.setActionOrder(rs.getLong("action_order")); 102 trigger.setActionStatement(rs.getString("action_statement")); 103 trigger.setActionTiming(rs.getString("action_timing")); 104 trigger.setSqlMode(rs.getString("sql_mode")); 105 trigger.setDefiner(rs.getString("definer")); 106 s.getTriggerMap().put(trigger.getName(), trigger); // needs to be unique across the schema I guess. 107 } 108 return null; 109 } 110 }); 111 } 112 113 // probably more efficient to read all tables at once, but this is what the current code does 114 private void readTables(final SchemaTO s) { 115 jt.query( 116 // TABLE_CATALOG always NULL, TABLE_SCHEMA 117 "SELECT TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, "+ 118 " DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, " + 119 " NUMERIC_PRECISION, NUMERIC_SCALE, CHARACTER_SET_NAME, " + 120 " COLLATION_NAME, COLUMN_TYPE, COLUMN_KEY, EXTRA, PRIVILEGES, " + 121 " COLUMN_COMMENT "+ 122 " FROM INFORMATION_SCHEMA.COLUMNS " + 123 " WHERE " + 124 " TABLE_SCHEMA = '" + s.getName() + "' " + // @TODO escape 125 " ORDER BY TABLE_NAME, ORDINAL_POSITION ", 126 new ResultSetExtractor<Object>() { 127 @Override 128 public Object extractData(ResultSet rs) throws SQLException, DataAccessException { 129 TableTO t = null; 130 String lastTableName = null; 131 while (rs.next()) { 132 String tableName = rs.getString("TABLE_NAME"); 133 if (t == null || !tableName.equals(lastTableName)) { 134 t = s.getTable(tableName); 135 lastTableName = tableName; 136 } 137 MysqlTableColumnTO mtc = new MysqlTableColumnTO(t, 138 t.getSchema().getDatabase().upper(rs.getString("COLUMN_NAME")), 139 rs.getLong("ORDINAL_POSITION"), 140 false, // name == "id" ? 141 rs.getString("DATA_TYPE"), 142 rs.getObject("CHARACTER_MAXIMUM_LENGTH") == null ? -1 : rs.getLong("CHARACTER_MAXIMUM_LENGTH"), 143 rs.getObject("NUMERIC_PRECISION") == null ? -1 : rs.getLong("NUMERIC_PRECISION"), 144 rs.getObject("NUMERIC_SCALE") == null ? -1 : rs.getLong("NUMERIC_SCALE"), 145 rs.getString("IS_NULLABLE").startsWith("Y") ? true : false, 146 rs.getString("COLUMN_DEFAULT"), 147 rs.getString("COLUMN_COMMENT")); 148 // String ct = rs.getString("COLUMN_TYPE"); 149 // logger.info("type for " + rs.getString("COLUMN_NAME") + " was " + ct); 150 // e.g. "int(10) unsigned", "enum('value1','value2','value3')" 151 mtc.setColumnType(rs.getString("COLUMN_TYPE")); 152 t.getTableColumnMap().put(mtc.getName(), mtc); 153 } 154 return null; 155 } 156 }); 157 158 jt.query( 159 "SELECT TC.constraint_catalog, TC.constraint_schema, TC.constraint_name,\n" + 160 " TC.table_schema, TC.table_name,\n" + 161 " TC.constraint_type, \n" + // TC.enforced in mysql 8 162 " KCU.column_name, KCU.ordinal_position, \n" + // -- KCU.position_in_unique_constraint = null 163 " KCU.referenced_table_schema, KCU.referenced_table_name, KCU.referenced_column_name\n" + 164 "FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC\n" + 165 " LEFT JOIN information_schema.KEY_COLUMN_USAGE KCU\n" + 166 " ON (TC.constraint_catalog = KCU.constraint_catalog\n" + 167 " AND TC.constraint_schema = KCU.constraint_schema\n" + 168 " AND TC.constraint_name = KCU.constraint_name\n" + 169 " AND TC.table_schema = KCU.table_schema\n" + 170 " AND TC.table_name = KCU.table_name)\n" + 171 "WHERE TC.table_schema = '" + s.getName() + "'\n" + // @TODO escape 172 " ORDER BY TC.table_schema, TC.table_name, TC.constraint_name, KCU.ordinal_position;\n", 173 new ResultSetExtractor<Object>() { 174 @Override 175 public Object extractData(ResultSet rs) throws SQLException, DataAccessException { 176 TableTO t = null; 177 ConstraintTO c = null; 178 String lastTableName = null; 179 String lastConstraintName = null; 180 181 while (rs.next()) { 182 String tableName = s.getDatabase().upper(rs.getString("TABLE_NAME")); 183 if (t == null || !tableName.equals(lastTableName)) { 184 t = s.getTable(tableName); 185 lastTableName = tableName; 186 lastConstraintName = null; 187 c = null; 188 } 189 190 String constraintName = s.getDatabase().upper(rs.getString("CONSTRAINT_NAME")); 191 if (c == null || !constraintName.equals(lastConstraintName)) { 192 String typeString = rs.getString("CONSTRAINT_TYPE"); 193 ConstraintTypeEnum type = ConstraintTypeEnum.fromDatabaseString(typeString); 194 c = new ConstraintTO(t, type, t.getSchema().getDatabase().upper(constraintName)); 195 t.getConstraintMap().put(constraintName, c); 196 lastConstraintName = constraintName; 197 } 198 199 ConstraintColumnTO cc = new ConstraintColumnTO(c, 200 c.getTable().getSchema().getDatabase().upper(rs.getString("COLUMN_NAME")), 201 rs.getLong("ORDINAL_POSITION"), 202 c.getTable().getSchema().getDatabase().upper(rs.getString("REFERENCED_TABLE_NAME")), 203 c.getTable().getSchema().getDatabase().upper(rs.getString("REFERENCED_COLUMN_NAME"))); 204 205 c.getConstraintColumnMap().put(cc.getName(), cc); 206 if (c.getConstraintType() == ConstraintTypeEnum.PRIMARY) { 207 if (t.getTableColumnMap().get(cc.getName()) == null) { 208 logger.error("Could not find column '" + cc.getName() + "' in table '" + t.getName() + "', tableName='" + tableName + "', constraintName = '" + constraintName + "'"); 209 } else { 210 t.getTableColumnMap().get(cc.getName()).setPrimaryKey(true); 211 } 212 } 213 } 214 return null; 215 } 216 }); 217 } 218 219 // probably more efficient to read all tables at once, but this is what the current code does 220 private void readRoutines(final SchemaTO s) { 221 jt.query( 222 // TABLE_CATALOG always NULL, TABLE_SCHEMA 223 "SELECT routine_catalog, routine_schema, routine_name, routine_type, "+ 224 " data_type, character_maximum_length, character_octet_length, " + 225 " numeric_precision, numeric_scale, character_set_name, " + 226 " collation_name, " + // dtd_identifier, routine_body 227 " routine_definition, " + // external_language, parameter_style 228 " is_deterministic, sql_data_access, sql_path, " + 229 " security_type, sql_mode, routine_comment, " + // created, last_altered 230 " definer " + // , character_set_client, collation_connection, database_collation 231 " FROM INFORMATION_SCHEMA.routines " + 232 " WHERE " + 233 " routine_schema = '" + s.getName() + "' " + // @TODO escape 234 " ORDER BY routine_name ", 235 new ResultSetExtractor<Object>() { 236 @Override 237 public Object extractData(ResultSet rs) throws SQLException, DataAccessException { 238 while (rs.next()) { 239 String routineName = rs.getString("routine_name"); 240 RoutineTO r = new RoutineTO(s, routineName); 241 r.setType(rs.getString("routine_type")); 242 r.setDataType("data_type"); 243 r.setDataTypeLength(rs.getLong("character_maximum_length")); if (rs.wasNull()) { r.setDataTypeLength(null); } 244 r.setDataTypeNumericPrecision(rs.getLong("numeric_precision")); if (rs.wasNull()) { r.setDataTypeNumericPrecision(null); } 245 r.setDataTypeNumericScale(rs.getLong("numeric_scale")); if (rs.wasNull()) { r.setDataTypeNumericScale(null); } 246 r.setCharsetName(rs.getString("character_set_name")); 247 r.setCollationName(rs.getString("collation_name")); 248 r.setDefinition(rs.getString("routine_definition")); 249 r.setDeterministic("YES".equals(rs.getString("is_deterministic"))); 250 r.setSecurityType(rs.getString("security_type")); 251 r.setSqlMode(rs.getString("sql_mode")); 252 r.setComment(rs.getString("routine_comment")); 253 r.setDefiner(rs.getString("definer")); 254 255 /* 256 RoutineParameterTO mtc = new RoutineParameterTO(t, 257 t.getSchema().getDatabase().upper(rs.getString("COLUMN_NAME")), 258 rs.getLong("ORDINAL_POSITION"), 259 false, // name == "id" ? 260 rs.getString("DATA_TYPE"), 261 rs.getObject("CHARACTER_MAXIMUM_LENGTH") == null ? -1 : rs.getLong("CHARACTER_MAXIMUM_LENGTH"), 262 rs.getObject("NUMERIC_PRECISION") == null ? -1 : rs.getLong("NUMERIC_PRECISION"), 263 rs.getObject("NUMERIC_SCALE") == null ? -1 : rs.getLong("NUMERIC_SCALE"), 264 rs.getString("IS_NULLABLE").startsWith("Y") ? true : false, 265 rs.getString("COLUMN_DEFAULT"), 266 rs.getString("COLUMN_COMMENT")); 267 // String ct = rs.getString("COLUMN_TYPE"); 268 // logger.info("type for " + rs.getString("COLUMN_NAME") + " was " + ct); 269 // e.g. "int(10) unsigned", "enum('value1','value2','value3')" 270 mtc.setColumnType(rs.getString("COLUMN_TYPE")); 271 t.getTableColumnMap().put(mtc.getName(), mtc); 272 */ 273 } 274 return null; 275 } 276 } 277 ); 278 279 } 280 281 282}