OpenStack Image Management (Glance)
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

042_add_changes_to_reinstall_unique_metadef_constraints.py 18KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442
  1. # Licensed under the Apache License, Version 2.0 (the "License"); you may
  2. # not use this file except in compliance with the License. You may obtain
  3. # a copy of the License at
  4. #
  5. # http://www.apache.org/licenses/LICENSE-2.0
  6. #
  7. # Unless required by applicable law or agreed to in writing, software
  8. # distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
  9. # WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
  10. # License for the specific language governing permissions and limitations
  11. # under the License.
  12. import migrate
  13. import sqlalchemy
  14. from sqlalchemy import (func, Index, inspect, orm, String, Table, type_coerce)
  15. # The _upgrade...get_duplicate() def's are separate functions to
  16. # accommodate sqlite which locks the database against updates as long as
  17. # db_recs is active.
  18. # In addition, sqlite doesn't support the function 'concat' between
  19. # Strings and Integers, so, the updating of records is also adjusted.
  20. def _upgrade_metadef_namespaces_get_duplicates(migrate_engine):
  21. meta = sqlalchemy.schema.MetaData(migrate_engine)
  22. metadef_namespaces = Table('metadef_namespaces', meta, autoload=True)
  23. session = orm.sessionmaker(bind=migrate_engine)()
  24. db_recs = (session.query(func.min(metadef_namespaces.c.id),
  25. metadef_namespaces.c.namespace)
  26. .group_by(metadef_namespaces.c.namespace)
  27. .having(func.count(metadef_namespaces.c.namespace) > 1))
  28. dbrecs = []
  29. for row in db_recs:
  30. dbrecs.append({'id': row[0], 'namespace': row[1]})
  31. session.close()
  32. return dbrecs
  33. def _upgrade_metadef_objects_get_duplicates(migrate_engine):
  34. meta = sqlalchemy.schema.MetaData(migrate_engine)
  35. metadef_objects = Table('metadef_objects', meta, autoload=True)
  36. session = orm.sessionmaker(bind=migrate_engine)()
  37. db_recs = (session.query(func.min(metadef_objects.c.id),
  38. metadef_objects.c.namespace_id,
  39. metadef_objects.c.name)
  40. .group_by(metadef_objects.c.namespace_id,
  41. metadef_objects.c.name)
  42. .having(func.count() > 1))
  43. dbrecs = []
  44. for row in db_recs:
  45. dbrecs.append({'id': row[0], 'namespace_id': row[1], 'name': row[2]})
  46. session.close()
  47. return dbrecs
  48. def _upgrade_metadef_properties_get_duplicates(migrate_engine):
  49. meta = sqlalchemy.schema.MetaData(migrate_engine)
  50. metadef_properties = Table('metadef_properties', meta, autoload=True)
  51. session = orm.sessionmaker(bind=migrate_engine)()
  52. db_recs = (session.query(func.min(metadef_properties.c.id),
  53. metadef_properties.c.namespace_id,
  54. metadef_properties.c.name)
  55. .group_by(metadef_properties.c.namespace_id,
  56. metadef_properties.c.name)
  57. .having(func.count() > 1))
  58. dbrecs = []
  59. for row in db_recs:
  60. dbrecs.append({'id': row[0], 'namespace_id': row[1], 'name': row[2]})
  61. session.close()
  62. return dbrecs
  63. def _upgrade_metadef_tags_get_duplicates(migrate_engine):
  64. meta = sqlalchemy.schema.MetaData(migrate_engine)
  65. metadef_tags = Table('metadef_tags', meta, autoload=True)
  66. session = orm.sessionmaker(bind=migrate_engine)()
  67. db_recs = (session.query(func.min(metadef_tags.c.id),
  68. metadef_tags.c.namespace_id,
  69. metadef_tags.c.name)
  70. .group_by(metadef_tags.c.namespace_id,
  71. metadef_tags.c.name)
  72. .having(func.count() > 1))
  73. dbrecs = []
  74. for row in db_recs:
  75. dbrecs.append({'id': row[0], 'namespace_id': row[1], 'name': row[2]})
  76. session.close()
  77. return dbrecs
  78. def _upgrade_metadef_resource_types_get_duplicates(migrate_engine):
  79. meta = sqlalchemy.schema.MetaData(migrate_engine)
  80. metadef_resource_types = Table('metadef_resource_types', meta,
  81. autoload=True)
  82. session = orm.sessionmaker(bind=migrate_engine)()
  83. db_recs = (session.query(func.min(metadef_resource_types.c.id),
  84. metadef_resource_types.c.name)
  85. .group_by(metadef_resource_types.c.name)
  86. .having(func.count(metadef_resource_types.c.name) > 1))
  87. dbrecs = []
  88. for row in db_recs:
  89. dbrecs.append({'id': row[0], 'name': row[1]})
  90. session.close()
  91. return dbrecs
  92. def _upgrade_data(migrate_engine):
  93. # Rename duplicates to be unique.
  94. meta = sqlalchemy.schema.MetaData(migrate_engine)
  95. # ORM tables
  96. metadef_namespaces = Table('metadef_namespaces', meta, autoload=True)
  97. metadef_objects = Table('metadef_objects', meta, autoload=True)
  98. metadef_properties = Table('metadef_properties', meta, autoload=True)
  99. metadef_tags = Table('metadef_tags', meta, autoload=True)
  100. metadef_resource_types = Table('metadef_resource_types', meta,
  101. autoload=True)
  102. # Fix duplicate metadef_namespaces
  103. # Update the non-first record(s) with an unique namespace value
  104. dbrecs = _upgrade_metadef_namespaces_get_duplicates(migrate_engine)
  105. for row in dbrecs:
  106. s = (metadef_namespaces.update()
  107. .where(metadef_namespaces.c.id > row['id'])
  108. .where(metadef_namespaces.c.namespace == row['namespace'])
  109. )
  110. if migrate_engine.name == 'sqlite':
  111. s = (s.values(namespace=(row['namespace'] + '-DUPL-' +
  112. type_coerce(metadef_namespaces.c.id,
  113. String)),
  114. display_name=(row['namespace'] + '-DUPL-' +
  115. type_coerce(metadef_namespaces.c.id,
  116. String))))
  117. else:
  118. s = s.values(namespace=func.concat(row['namespace'],
  119. '-DUPL-',
  120. metadef_namespaces.c.id),
  121. display_name=func.concat(row['namespace'],
  122. '-DUPL-',
  123. metadef_namespaces.c.id))
  124. s.execute()
  125. # Fix duplicate metadef_objects
  126. dbrecs = _upgrade_metadef_objects_get_duplicates(migrate_engine)
  127. for row in dbrecs:
  128. s = (metadef_objects.update()
  129. .where(metadef_objects.c.id > row['id'])
  130. .where(metadef_objects.c.namespace_id == row['namespace_id'])
  131. .where(metadef_objects.c.name == str(row['name']))
  132. )
  133. if migrate_engine.name == 'sqlite':
  134. s = (s.values(name=(row['name'] + '-DUPL-'
  135. + type_coerce(metadef_objects.c.id, String))))
  136. else:
  137. s = s.values(name=func.concat(row['name'], '-DUPL-',
  138. metadef_objects.c.id))
  139. s.execute()
  140. # Fix duplicate metadef_properties
  141. dbrecs = _upgrade_metadef_properties_get_duplicates(migrate_engine)
  142. for row in dbrecs:
  143. s = (metadef_properties.update()
  144. .where(metadef_properties.c.id > row['id'])
  145. .where(metadef_properties.c.namespace_id == row['namespace_id'])
  146. .where(metadef_properties.c.name == str(row['name']))
  147. )
  148. if migrate_engine.name == 'sqlite':
  149. s = (s.values(name=(row['name'] + '-DUPL-' +
  150. type_coerce(metadef_properties.c.id, String)))
  151. )
  152. else:
  153. s = s.values(name=func.concat(row['name'], '-DUPL-',
  154. metadef_properties.c.id))
  155. s.execute()
  156. # Fix duplicate metadef_tags
  157. dbrecs = _upgrade_metadef_tags_get_duplicates(migrate_engine)
  158. for row in dbrecs:
  159. s = (metadef_tags.update()
  160. .where(metadef_tags.c.id > row['id'])
  161. .where(metadef_tags.c.namespace_id == row['namespace_id'])
  162. .where(metadef_tags.c.name == str(row['name']))
  163. )
  164. if migrate_engine.name == 'sqlite':
  165. s = (s.values(name=(row['name'] + '-DUPL-' +
  166. type_coerce(metadef_tags.c.id, String)))
  167. )
  168. else:
  169. s = s.values(name=func.concat(row['name'], '-DUPL-',
  170. metadef_tags.c.id))
  171. s.execute()
  172. # Fix duplicate metadef_resource_types
  173. dbrecs = _upgrade_metadef_resource_types_get_duplicates(migrate_engine)
  174. for row in dbrecs:
  175. s = (metadef_resource_types.update()
  176. .where(metadef_resource_types.c.id > row['id'])
  177. .where(metadef_resource_types.c.name == str(row['name']))
  178. )
  179. if migrate_engine.name == 'sqlite':
  180. s = (s.values(name=(row['name'] + '-DUPL-' +
  181. type_coerce(metadef_resource_types.c.id,
  182. String)))
  183. )
  184. else:
  185. s = s.values(name=func.concat(row['name'], '-DUPL-',
  186. metadef_resource_types.c.id))
  187. s.execute()
  188. def _update_sqlite_namespace_id_name_constraint(metadef, metadef_namespaces,
  189. new_constraint_name,
  190. new_fk_name):
  191. migrate.UniqueConstraint(
  192. metadef.c.namespace_id, metadef.c.name).drop()
  193. migrate.UniqueConstraint(
  194. metadef.c.namespace_id, metadef.c.name,
  195. name=new_constraint_name).create()
  196. migrate.ForeignKeyConstraint(
  197. [metadef.c.namespace_id],
  198. [metadef_namespaces.c.id],
  199. name=new_fk_name).create()
  200. def _drop_unique_constraint_if_exists(inspector, table_name, metadef):
  201. name = _get_unique_constraint_name(inspector,
  202. table_name,
  203. ['namespace_id', 'name'])
  204. if name:
  205. migrate.UniqueConstraint(metadef.c.namespace_id,
  206. metadef.c.name,
  207. name=name).drop()
  208. def _drop_index_with_fk_constraint(metadef, metadef_namespaces,
  209. index_name,
  210. fk_old_name, fk_new_name):
  211. fkc = migrate.ForeignKeyConstraint([metadef.c.namespace_id],
  212. [metadef_namespaces.c.id],
  213. name=fk_old_name)
  214. fkc.drop()
  215. if index_name:
  216. Index(index_name, metadef.c.namespace_id).drop()
  217. # Rename the fk for consistency across all db's
  218. fkc = migrate.ForeignKeyConstraint([metadef.c.namespace_id],
  219. [metadef_namespaces.c.id],
  220. name=fk_new_name)
  221. fkc.create()
  222. def _get_unique_constraint_name(inspector, table_name, columns):
  223. constraints = inspector.get_unique_constraints(table_name)
  224. for constraint in constraints:
  225. if set(constraint['column_names']) == set(columns):
  226. return constraint['name']
  227. return None
  228. def _get_fk_constraint_name(inspector, table_name, columns):
  229. constraints = inspector.get_foreign_keys(table_name)
  230. for constraint in constraints:
  231. if set(constraint['constrained_columns']) == set(columns):
  232. return constraint['name']
  233. return None
  234. def upgrade(migrate_engine):
  235. _upgrade_data(migrate_engine)
  236. meta = sqlalchemy.MetaData()
  237. meta.bind = migrate_engine
  238. inspector = inspect(migrate_engine)
  239. # ORM tables
  240. metadef_namespaces = Table('metadef_namespaces', meta, autoload=True)
  241. metadef_objects = Table('metadef_objects', meta, autoload=True)
  242. metadef_properties = Table('metadef_properties', meta, autoload=True)
  243. metadef_tags = Table('metadef_tags', meta, autoload=True)
  244. metadef_ns_res_types = Table('metadef_namespace_resource_types',
  245. meta, autoload=True)
  246. metadef_resource_types = Table('metadef_resource_types', meta,
  247. autoload=True)
  248. # Drop the bad, non-unique indices.
  249. if migrate_engine.name == 'sqlite':
  250. # For sqlite:
  251. # Only after the unique constraints have been added should the indices
  252. # be dropped. If done the other way, sqlite complains during
  253. # constraint adding/dropping that the index does/does not exist.
  254. # Note: The _get_unique_constraint_name, _get_fk_constraint_name
  255. # return None for constraints that do in fact exist. Also,
  256. # get_index_names returns names, but, the names can not be used with
  257. # the Index(name, blah).drop() command, so, putting sqlite into
  258. # it's own section.
  259. # Objects
  260. _update_sqlite_namespace_id_name_constraint(
  261. metadef_objects, metadef_namespaces,
  262. 'uq_metadef_objects_namespace_id_name',
  263. 'metadef_objects_fk_1')
  264. # Properties
  265. _update_sqlite_namespace_id_name_constraint(
  266. metadef_properties, metadef_namespaces,
  267. 'uq_metadef_properties_namespace_id_name',
  268. 'metadef_properties_fk_1')
  269. # Tags
  270. _update_sqlite_namespace_id_name_constraint(
  271. metadef_tags, metadef_namespaces,
  272. 'uq_metadef_tags_namespace_id_name',
  273. 'metadef_tags_fk_1')
  274. # Namespaces
  275. migrate.UniqueConstraint(
  276. metadef_namespaces.c.namespace).drop()
  277. migrate.UniqueConstraint(
  278. metadef_namespaces.c.namespace,
  279. name='uq_metadef_namespaces_namespace').create()
  280. # ResourceTypes
  281. migrate.UniqueConstraint(
  282. metadef_resource_types.c.name).drop()
  283. migrate.UniqueConstraint(
  284. metadef_resource_types.c.name,
  285. name='uq_metadef_resource_types_name').create()
  286. # Now drop the bad indices
  287. Index('ix_metadef_objects_namespace_id',
  288. metadef_objects.c.namespace_id,
  289. metadef_objects.c.name).drop()
  290. Index('ix_metadef_properties_namespace_id',
  291. metadef_properties.c.namespace_id,
  292. metadef_properties.c.name).drop()
  293. Index('ix_metadef_tags_namespace_id',
  294. metadef_tags.c.namespace_id,
  295. metadef_tags.c.name).drop()
  296. else:
  297. # First drop the bad non-unique indices.
  298. # To do that (for mysql), must first drop foreign key constraints
  299. # BY NAME and then drop the bad indices.
  300. # Finally, re-create the foreign key constraints with a consistent
  301. # name.
  302. # DB2 still has unique constraints, but, they are badly named.
  303. # Drop them, they will be recreated at the final step.
  304. name = _get_unique_constraint_name(inspector, 'metadef_namespaces',
  305. ['namespace'])
  306. if name:
  307. migrate.UniqueConstraint(metadef_namespaces.c.namespace,
  308. name=name).drop()
  309. _drop_unique_constraint_if_exists(inspector, 'metadef_objects',
  310. metadef_objects)
  311. _drop_unique_constraint_if_exists(inspector, 'metadef_properties',
  312. metadef_properties)
  313. _drop_unique_constraint_if_exists(inspector, 'metadef_tags',
  314. metadef_tags)
  315. name = _get_unique_constraint_name(inspector, 'metadef_resource_types',
  316. ['name'])
  317. if name:
  318. migrate.UniqueConstraint(metadef_resource_types.c.name,
  319. name=name).drop()
  320. # Objects
  321. _drop_index_with_fk_constraint(
  322. metadef_objects, metadef_namespaces,
  323. 'ix_metadef_objects_namespace_id',
  324. _get_fk_constraint_name(
  325. inspector, 'metadef_objects', ['namespace_id']),
  326. 'metadef_objects_fk_1')
  327. # Properties
  328. _drop_index_with_fk_constraint(
  329. metadef_properties, metadef_namespaces,
  330. 'ix_metadef_properties_namespace_id',
  331. _get_fk_constraint_name(
  332. inspector, 'metadef_properties', ['namespace_id']),
  333. 'metadef_properties_fk_1')
  334. # Tags
  335. _drop_index_with_fk_constraint(
  336. metadef_tags, metadef_namespaces,
  337. 'ix_metadef_tags_namespace_id',
  338. _get_fk_constraint_name(
  339. inspector, 'metadef_tags', ['namespace_id']),
  340. 'metadef_tags_fk_1')
  341. # Drop Others without fk constraints.
  342. Index('ix_metadef_namespaces_namespace',
  343. metadef_namespaces.c.namespace).drop()
  344. # The next two don't exist in ibm_db_sa, but, drop them everywhere else.
  345. if migrate_engine.name != 'ibm_db_sa':
  346. Index('ix_metadef_resource_types_name',
  347. metadef_resource_types.c.name).drop()
  348. # Not needed due to primary key on same columns
  349. Index('ix_metadef_ns_res_types_res_type_id_ns_id',
  350. metadef_ns_res_types.c.resource_type_id,
  351. metadef_ns_res_types.c.namespace_id).drop()
  352. # Now, add back the dropped indexes as unique constraints
  353. if migrate_engine.name != 'sqlite':
  354. # Namespaces
  355. migrate.UniqueConstraint(
  356. metadef_namespaces.c.namespace,
  357. name='uq_metadef_namespaces_namespace').create()
  358. # Objects
  359. migrate.UniqueConstraint(
  360. metadef_objects.c.namespace_id,
  361. metadef_objects.c.name,
  362. name='uq_metadef_objects_namespace_id_name').create()
  363. # Properties
  364. migrate.UniqueConstraint(
  365. metadef_properties.c.namespace_id,
  366. metadef_properties.c.name,
  367. name='uq_metadef_properties_namespace_id_name').create()
  368. # Tags
  369. migrate.UniqueConstraint(
  370. metadef_tags.c.namespace_id,
  371. metadef_tags.c.name,
  372. name='uq_metadef_tags_namespace_id_name').create()
  373. # Resource Types
  374. migrate.UniqueConstraint(
  375. metadef_resource_types.c.name,
  376. name='uq_metadef_resource_types_name').create()