Hide keyboard shortcuts

Hot-keys on this page

r m x p   toggle line displays

j k   next/prev highlighted chunk

0   (zero) top of page

1   (one) first highlighted chunk

1import re 

2 

3from sqlalchemy import schema 

4from sqlalchemy import types as sqltypes 

5from sqlalchemy.ext.compiler import compiles 

6 

7from .base import alter_table 

8from .base import AlterColumn 

9from .base import ColumnDefault 

10from .base import ColumnName 

11from .base import ColumnNullable 

12from .base import ColumnType 

13from .base import format_column_name 

14from .base import format_server_default 

15from .impl import DefaultImpl 

16from .. import util 

17from ..autogenerate import compare 

18from ..util.compat import string_types 

19from ..util.sqla_compat import _is_mariadb 

20from ..util.sqla_compat import _is_type_bound 

21 

22 

23class MySQLImpl(DefaultImpl): 

24 __dialect__ = "mysql" 

25 

26 transactional_ddl = False 

27 type_synonyms = DefaultImpl.type_synonyms + ({"BOOL", "TINYINT"},) 

28 type_arg_extract = [r"character set ([\w\-_]+)", r"collate ([\w\-_]+)"] 

29 

30 def alter_column( 

31 self, 

32 table_name, 

33 column_name, 

34 nullable=None, 

35 server_default=False, 

36 name=None, 

37 type_=None, 

38 schema=None, 

39 existing_type=None, 

40 existing_server_default=None, 

41 existing_nullable=None, 

42 autoincrement=None, 

43 existing_autoincrement=None, 

44 comment=False, 

45 existing_comment=None, 

46 **kw 

47 ): 

48 if name is not None or self._is_mysql_allowed_functional_default( 

49 type_ if type_ is not None else existing_type, server_default 

50 ): 

51 self._exec( 

52 MySQLChangeColumn( 

53 table_name, 

54 column_name, 

55 schema=schema, 

56 newname=name if name is not None else column_name, 

57 nullable=nullable 

58 if nullable is not None 

59 else existing_nullable 

60 if existing_nullable is not None 

61 else True, 

62 type_=type_ if type_ is not None else existing_type, 

63 default=server_default 

64 if server_default is not False 

65 else existing_server_default, 

66 autoincrement=autoincrement 

67 if autoincrement is not None 

68 else existing_autoincrement, 

69 comment=comment 

70 if comment is not False 

71 else existing_comment, 

72 ) 

73 ) 

74 elif ( 

75 nullable is not None 

76 or type_ is not None 

77 or autoincrement is not None 

78 or comment is not False 

79 ): 

80 self._exec( 

81 MySQLModifyColumn( 

82 table_name, 

83 column_name, 

84 schema=schema, 

85 newname=name if name is not None else column_name, 

86 nullable=nullable 

87 if nullable is not None 

88 else existing_nullable 

89 if existing_nullable is not None 

90 else True, 

91 type_=type_ if type_ is not None else existing_type, 

92 default=server_default 

93 if server_default is not False 

94 else existing_server_default, 

95 autoincrement=autoincrement 

96 if autoincrement is not None 

97 else existing_autoincrement, 

98 comment=comment 

99 if comment is not False 

100 else existing_comment, 

101 ) 

102 ) 

103 elif server_default is not False: 

104 self._exec( 

105 MySQLAlterDefault( 

106 table_name, column_name, server_default, schema=schema 

107 ) 

108 ) 

109 

110 def drop_constraint(self, const): 

111 if isinstance(const, schema.CheckConstraint) and _is_type_bound(const): 

112 return 

113 

114 super(MySQLImpl, self).drop_constraint(const) 

115 

116 def _is_mysql_allowed_functional_default(self, type_, server_default): 

117 return ( 

118 type_ is not None 

119 and type_._type_affinity is sqltypes.DateTime 

120 and server_default is not None 

121 ) 

122 

123 def compare_server_default( 

124 self, 

125 inspector_column, 

126 metadata_column, 

127 rendered_metadata_default, 

128 rendered_inspector_default, 

129 ): 

130 # partially a workaround for SQLAlchemy issue #3023; if the 

131 # column were created without "NOT NULL", MySQL may have added 

132 # an implicit default of '0' which we need to skip 

133 # TODO: this is not really covered anymore ? 

134 if ( 

135 metadata_column.type._type_affinity is sqltypes.Integer 

136 and inspector_column.primary_key 

137 and not inspector_column.autoincrement 

138 and not rendered_metadata_default 

139 and rendered_inspector_default == "'0'" 

140 ): 

141 return False 

142 elif inspector_column.type._type_affinity is sqltypes.Integer: 

143 rendered_inspector_default = ( 

144 re.sub(r"^'|'$", "", rendered_inspector_default) 

145 if rendered_inspector_default is not None 

146 else None 

147 ) 

148 return rendered_inspector_default != rendered_metadata_default 

149 elif rendered_inspector_default and rendered_metadata_default: 

150 # adjust for "function()" vs. "FUNCTION" as can occur particularly 

151 # for the CURRENT_TIMESTAMP function on newer MariaDB versions 

152 

153 # SQLAlchemy MySQL dialect bundles ON UPDATE into the server 

154 # default; adjust for this possibly being present. 

155 onupdate_ins = re.match( 

156 r"(.*) (on update.*?)(?:\(\))?$", 

157 rendered_inspector_default.lower(), 

158 ) 

159 onupdate_met = re.match( 

160 r"(.*) (on update.*?)(?:\(\))?$", 

161 rendered_metadata_default.lower(), 

162 ) 

163 

164 if onupdate_ins: 

165 if not onupdate_met: 

166 return True 

167 elif onupdate_ins.group(2) != onupdate_met.group(2): 

168 return True 

169 

170 rendered_inspector_default = onupdate_ins.group(1) 

171 rendered_metadata_default = onupdate_met.group(1) 

172 

173 return re.sub( 

174 r"(.*?)(?:\(\))?$", r"\1", rendered_inspector_default.lower() 

175 ) != re.sub( 

176 r"(.*?)(?:\(\))?$", r"\1", rendered_metadata_default.lower() 

177 ) 

178 else: 

179 return rendered_inspector_default != rendered_metadata_default 

180 

181 def correct_for_autogen_constraints( 

182 self, 

183 conn_unique_constraints, 

184 conn_indexes, 

185 metadata_unique_constraints, 

186 metadata_indexes, 

187 ): 

188 

189 # TODO: if SQLA 1.0, make use of "duplicates_index" 

190 # metadata 

191 removed = set() 

192 for idx in list(conn_indexes): 

193 if idx.unique: 

194 continue 

195 # MySQL puts implicit indexes on FK columns, even if 

196 # composite and even if MyISAM, so can't check this too easily. 

197 # the name of the index may be the column name or it may 

198 # be the name of the FK constraint. 

199 for col in idx.columns: 

200 if idx.name == col.name: 

201 conn_indexes.remove(idx) 

202 removed.add(idx.name) 

203 break 

204 for fk in col.foreign_keys: 

205 if fk.name == idx.name: 

206 conn_indexes.remove(idx) 

207 removed.add(idx.name) 

208 break 

209 if idx.name in removed: 

210 break 

211 

212 # then remove indexes from the "metadata_indexes" 

213 # that we've removed from reflected, otherwise they come out 

214 # as adds (see #202) 

215 for idx in list(metadata_indexes): 

216 if idx.name in removed: 

217 metadata_indexes.remove(idx) 

218 

219 def correct_for_autogen_foreignkeys(self, conn_fks, metadata_fks): 

220 conn_fk_by_sig = dict( 

221 (compare._fk_constraint_sig(fk).sig, fk) for fk in conn_fks 

222 ) 

223 metadata_fk_by_sig = dict( 

224 (compare._fk_constraint_sig(fk).sig, fk) for fk in metadata_fks 

225 ) 

226 

227 for sig in set(conn_fk_by_sig).intersection(metadata_fk_by_sig): 

228 mdfk = metadata_fk_by_sig[sig] 

229 cnfk = conn_fk_by_sig[sig] 

230 # MySQL considers RESTRICT to be the default and doesn't 

231 # report on it. if the model has explicit RESTRICT and 

232 # the conn FK has None, set it to RESTRICT 

233 if ( 

234 mdfk.ondelete is not None 

235 and mdfk.ondelete.lower() == "restrict" 

236 and cnfk.ondelete is None 

237 ): 

238 cnfk.ondelete = "RESTRICT" 

239 if ( 

240 mdfk.onupdate is not None 

241 and mdfk.onupdate.lower() == "restrict" 

242 and cnfk.onupdate is None 

243 ): 

244 cnfk.onupdate = "RESTRICT" 

245 

246 

247class MySQLAlterDefault(AlterColumn): 

248 def __init__(self, name, column_name, default, schema=None): 

249 super(AlterColumn, self).__init__(name, schema=schema) 

250 self.column_name = column_name 

251 self.default = default 

252 

253 

254class MySQLChangeColumn(AlterColumn): 

255 def __init__( 

256 self, 

257 name, 

258 column_name, 

259 schema=None, 

260 newname=None, 

261 type_=None, 

262 nullable=None, 

263 default=False, 

264 autoincrement=None, 

265 comment=False, 

266 ): 

267 super(AlterColumn, self).__init__(name, schema=schema) 

268 self.column_name = column_name 

269 self.nullable = nullable 

270 self.newname = newname 

271 self.default = default 

272 self.autoincrement = autoincrement 

273 self.comment = comment 

274 if type_ is None: 

275 raise util.CommandError( 

276 "All MySQL CHANGE/MODIFY COLUMN operations " 

277 "require the existing type." 

278 ) 

279 

280 self.type_ = sqltypes.to_instance(type_) 

281 

282 

283class MySQLModifyColumn(MySQLChangeColumn): 

284 pass 

285 

286 

287@compiles(ColumnNullable, "mysql") 

288@compiles(ColumnName, "mysql") 

289@compiles(ColumnDefault, "mysql") 

290@compiles(ColumnType, "mysql") 

291def _mysql_doesnt_support_individual(element, compiler, **kw): 

292 raise NotImplementedError( 

293 "Individual alter column constructs not supported by MySQL" 

294 ) 

295 

296 

297@compiles(MySQLAlterDefault, "mysql") 

298def _mysql_alter_default(element, compiler, **kw): 

299 return "%s ALTER COLUMN %s %s" % ( 

300 alter_table(compiler, element.table_name, element.schema), 

301 format_column_name(compiler, element.column_name), 

302 "SET DEFAULT %s" % format_server_default(compiler, element.default) 

303 if element.default is not None 

304 else "DROP DEFAULT", 

305 ) 

306 

307 

308@compiles(MySQLModifyColumn, "mysql") 

309def _mysql_modify_column(element, compiler, **kw): 

310 return "%s MODIFY %s %s" % ( 

311 alter_table(compiler, element.table_name, element.schema), 

312 format_column_name(compiler, element.column_name), 

313 _mysql_colspec( 

314 compiler, 

315 nullable=element.nullable, 

316 server_default=element.default, 

317 type_=element.type_, 

318 autoincrement=element.autoincrement, 

319 comment=element.comment, 

320 ), 

321 ) 

322 

323 

324@compiles(MySQLChangeColumn, "mysql") 

325def _mysql_change_column(element, compiler, **kw): 

326 return "%s CHANGE %s %s %s" % ( 

327 alter_table(compiler, element.table_name, element.schema), 

328 format_column_name(compiler, element.column_name), 

329 format_column_name(compiler, element.newname), 

330 _mysql_colspec( 

331 compiler, 

332 nullable=element.nullable, 

333 server_default=element.default, 

334 type_=element.type_, 

335 autoincrement=element.autoincrement, 

336 comment=element.comment, 

337 ), 

338 ) 

339 

340 

341def _render_value(compiler, expr): 

342 if isinstance(expr, string_types): 

343 return "'%s'" % expr 

344 else: 

345 return compiler.sql_compiler.process(expr) 

346 

347 

348def _mysql_colspec( 

349 compiler, nullable, server_default, type_, autoincrement, comment 

350): 

351 spec = "%s %s" % ( 

352 compiler.dialect.type_compiler.process(type_), 

353 "NULL" if nullable else "NOT NULL", 

354 ) 

355 if autoincrement: 

356 spec += " AUTO_INCREMENT" 

357 if server_default is not False and server_default is not None: 

358 spec += " DEFAULT %s" % _render_value(compiler, server_default) 

359 if comment: 

360 spec += " COMMENT %s" % compiler.sql_compiler.render_literal_value( 

361 comment, sqltypes.String() 

362 ) 

363 

364 return spec 

365 

366 

367@compiles(schema.DropConstraint, "mysql") 

368def _mysql_drop_constraint(element, compiler, **kw): 

369 """Redefine SQLAlchemy's drop constraint to 

370 raise errors for invalid constraint type.""" 

371 

372 constraint = element.element 

373 if isinstance( 

374 constraint, 

375 ( 

376 schema.ForeignKeyConstraint, 

377 schema.PrimaryKeyConstraint, 

378 schema.UniqueConstraint, 

379 ), 

380 ): 

381 return compiler.visit_drop_constraint(element, **kw) 

382 elif isinstance(constraint, schema.CheckConstraint): 

383 # note that SQLAlchemy as of 1.2 does not yet support 

384 # DROP CONSTRAINT for MySQL/MariaDB, so we implement fully 

385 # here. 

386 if _is_mariadb(compiler.dialect): 

387 return "ALTER TABLE %s DROP CONSTRAINT %s" % ( 

388 compiler.preparer.format_table(constraint.table), 

389 compiler.preparer.format_constraint(constraint), 

390 ) 

391 else: 

392 return "ALTER TABLE %s DROP CHECK %s" % ( 

393 compiler.preparer.format_table(constraint.table), 

394 compiler.preparer.format_constraint(constraint), 

395 ) 

396 else: 

397 raise NotImplementedError( 

398 "No generic 'DROP CONSTRAINT' in MySQL - " 

399 "please specify constraint type" 

400 )