/lib/galaxy/model/migrate/versions/0003_security_and_libraries.py

https://bitbucket.org/cistrome/cistrome-harvard/ · Python · 783 lines · 707 code · 42 blank · 34 comment · 169 complexity · 5efaaa2a7f122aef31fb859f0da3d23d MD5 · raw file

  1. from sqlalchemy import *
  2. from sqlalchemy.orm import *
  3. from sqlalchemy.exc import *
  4. from migrate import *
  5. from migrate.changeset import *
  6. import datetime
  7. now = datetime.datetime.utcnow
  8. import sys, logging
  9. log = logging.getLogger( __name__ )
  10. log.setLevel(logging.DEBUG)
  11. handler = logging.StreamHandler( sys.stdout )
  12. format = "%(name)s %(levelname)s %(asctime)s %(message)s"
  13. formatter = logging.Formatter( format )
  14. handler.setFormatter( formatter )
  15. log.addHandler( handler )
  16. # Need our custom types, but don't import anything else from model
  17. from galaxy.model.custom_types import *
  18. metadata = MetaData()
  19. # New tables as of changeset 2341:5498ac35eedd
  20. Group_table = Table( "galaxy_group", metadata,
  21. Column( "id", Integer, primary_key=True ),
  22. Column( "create_time", DateTime, default=now ),
  23. Column( "update_time", DateTime, default=now, onupdate=now ),
  24. Column( "name", String( 255 ), index=True, unique=True ),
  25. Column( "deleted", Boolean, index=True, default=False ) )
  26. UserGroupAssociation_table = Table( "user_group_association", metadata,
  27. Column( "id", Integer, primary_key=True ),
  28. Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
  29. Column( "group_id", Integer, ForeignKey( "galaxy_group.id" ), index=True ),
  30. Column( "create_time", DateTime, default=now ),
  31. Column( "update_time", DateTime, default=now, onupdate=now ) )
  32. UserRoleAssociation_table = Table( "user_role_association", metadata,
  33. Column( "id", Integer, primary_key=True ),
  34. Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
  35. Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ),
  36. Column( "create_time", DateTime, default=now ),
  37. Column( "update_time", DateTime, default=now, onupdate=now ) )
  38. GroupRoleAssociation_table = Table( "group_role_association", metadata,
  39. Column( "id", Integer, primary_key=True ),
  40. Column( "group_id", Integer, ForeignKey( "galaxy_group.id" ), index=True ),
  41. Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ),
  42. Column( "create_time", DateTime, default=now ),
  43. Column( "update_time", DateTime, default=now, onupdate=now ) )
  44. Role_table = Table( "role", metadata,
  45. Column( "id", Integer, primary_key=True ),
  46. Column( "create_time", DateTime, default=now ),
  47. Column( "update_time", DateTime, default=now, onupdate=now ),
  48. Column( "name", String( 255 ), index=True, unique=True ),
  49. Column( "description", TEXT ),
  50. Column( "type", String( 40 ), index=True ),
  51. Column( "deleted", Boolean, index=True, default=False ) )
  52. DatasetPermissions_table = Table( "dataset_permissions", metadata,
  53. Column( "id", Integer, primary_key=True ),
  54. Column( "create_time", DateTime, default=now ),
  55. Column( "update_time", DateTime, default=now, onupdate=now ),
  56. Column( "action", TEXT ),
  57. Column( "dataset_id", Integer, ForeignKey( "dataset.id" ), index=True ),
  58. Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
  59. LibraryPermissions_table = Table( "library_permissions", metadata,
  60. Column( "id", Integer, primary_key=True ),
  61. Column( "create_time", DateTime, default=now ),
  62. Column( "update_time", DateTime, default=now, onupdate=now ),
  63. Column( "action", TEXT ),
  64. Column( "library_id", Integer, ForeignKey( "library.id" ), nullable=True, index=True ),
  65. Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
  66. LibraryFolderPermissions_table = Table( "library_folder_permissions", metadata,
  67. Column( "id", Integer, primary_key=True ),
  68. Column( "create_time", DateTime, default=now ),
  69. Column( "update_time", DateTime, default=now, onupdate=now ),
  70. Column( "action", TEXT ),
  71. Column( "library_folder_id", Integer, ForeignKey( "library_folder.id" ), nullable=True, index=True ),
  72. Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
  73. LibraryDatasetPermissions_table = Table( "library_dataset_permissions", metadata,
  74. Column( "id", Integer, primary_key=True ),
  75. Column( "create_time", DateTime, default=now ),
  76. Column( "update_time", DateTime, default=now, onupdate=now ),
  77. Column( "action", TEXT ),
  78. Column( "library_dataset_id", Integer, ForeignKey( "library_dataset.id" ), nullable=True, index=True ),
  79. Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
  80. LibraryDatasetDatasetAssociationPermissions_table = Table( "library_dataset_dataset_association_permissions", metadata,
  81. Column( "id", Integer, primary_key=True ),
  82. Column( "create_time", DateTime, default=now ),
  83. Column( "update_time", DateTime, default=now, onupdate=now ),
  84. Column( "action", TEXT ),
  85. Column( "library_dataset_dataset_association_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), nullable=True ),
  86. Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
  87. Index( "ix_lddap_library_dataset_dataset_association_id", LibraryDatasetDatasetAssociationPermissions_table.c.library_dataset_dataset_association_id )
  88. LibraryItemInfoPermissions_table = Table( "library_item_info_permissions", metadata,
  89. Column( "id", Integer, primary_key=True ),
  90. Column( "create_time", DateTime, default=now ),
  91. Column( "update_time", DateTime, default=now, onupdate=now ),
  92. Column( "action", TEXT ),
  93. Column( "library_item_info_id", Integer, ForeignKey( "library_item_info.id" ), nullable=True, index=True ),
  94. Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
  95. LibraryItemInfoTemplatePermissions_table = Table( "library_item_info_template_permissions", metadata,
  96. Column( "id", Integer, primary_key=True ),
  97. Column( "create_time", DateTime, default=now ),
  98. Column( "update_time", DateTime, default=now, onupdate=now ),
  99. Column( "action", TEXT ),
  100. Column( "library_item_info_template_id", Integer, ForeignKey( "library_item_info_template.id" ), nullable=True ),
  101. Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
  102. Index( "ix_liitp_library_item_info_template_id", LibraryItemInfoTemplatePermissions_table.c.library_item_info_template_id )
  103. DefaultUserPermissions_table = Table( "default_user_permissions", metadata,
  104. Column( "id", Integer, primary_key=True ),
  105. Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
  106. Column( "action", TEXT ),
  107. Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
  108. DefaultHistoryPermissions_table = Table( "default_history_permissions", metadata,
  109. Column( "id", Integer, primary_key=True ),
  110. Column( "history_id", Integer, ForeignKey( "history.id" ), index=True ),
  111. Column( "action", TEXT ),
  112. Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )
  113. LibraryDataset_table = Table( "library_dataset", metadata,
  114. Column( "id", Integer, primary_key=True ),
  115. Column( "library_dataset_dataset_association_id", Integer, ForeignKey( "library_dataset_dataset_association.id", use_alter=True, name="library_dataset_dataset_association_id_fk" ), nullable=True, index=True ),#current version of dataset, if null, there is not a current version selected
  116. Column( "folder_id", Integer, ForeignKey( "library_folder.id" ), index=True ),
  117. Column( "order_id", Integer ),
  118. Column( "create_time", DateTime, default=now ),
  119. Column( "update_time", DateTime, default=now, onupdate=now ),
  120. Column( "name", TrimmedString( 255 ), key="_name" ), #when not None/null this will supercede display in library (but not when imported into user's history?)
  121. Column( "info", TrimmedString( 255 ), key="_info" ), #when not None/null this will supercede display in library (but not when imported into user's history?)
  122. Column( "deleted", Boolean, index=True, default=False ) )
  123. LibraryDatasetDatasetAssociation_table = Table( "library_dataset_dataset_association", metadata,
  124. Column( "id", Integer, primary_key=True ),
  125. Column( "library_dataset_id", Integer, ForeignKey( "library_dataset.id" ), index=True ),
  126. Column( "dataset_id", Integer, ForeignKey( "dataset.id" ), index=True ),
  127. Column( "create_time", DateTime, default=now ),
  128. Column( "update_time", DateTime, default=now, onupdate=now ),
  129. Column( "copied_from_history_dataset_association_id", Integer, ForeignKey( "history_dataset_association.id", use_alter=True, name='history_dataset_association_dataset_id_fkey' ), nullable=True ),
  130. Column( "copied_from_library_dataset_dataset_association_id", Integer, ForeignKey( "library_dataset_dataset_association.id", use_alter=True, name='library_dataset_dataset_association_id_fkey' ), nullable=True ),
  131. Column( "name", TrimmedString( 255 ) ),
  132. Column( "info", TrimmedString( 255 ) ),
  133. Column( "blurb", TrimmedString( 255 ) ),
  134. Column( "peek" , TEXT ),
  135. Column( "extension", TrimmedString( 64 ) ),
  136. Column( "metadata", MetadataType(), key="_metadata" ),
  137. Column( "parent_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), nullable=True ),
  138. Column( "designation", TrimmedString( 255 ) ),
  139. Column( "deleted", Boolean, index=True, default=False ),
  140. Column( "visible", Boolean ),
  141. Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
  142. Column( "message", TrimmedString( 255 ) ) )
  143. Library_table = Table( "library", metadata,
  144. Column( "id", Integer, primary_key=True ),
  145. Column( "root_folder_id", Integer, ForeignKey( "library_folder.id" ), index=True ),
  146. Column( "create_time", DateTime, default=now ),
  147. Column( "update_time", DateTime, default=now, onupdate=now ),
  148. Column( "name", String( 255 ), index=True ),
  149. Column( "deleted", Boolean, index=True, default=False ),
  150. Column( "purged", Boolean, index=True, default=False ),
  151. Column( "description", TEXT ) )
  152. LibraryFolder_table = Table( "library_folder", metadata,
  153. Column( "id", Integer, primary_key=True ),
  154. Column( "parent_id", Integer, ForeignKey( "library_folder.id" ), nullable = True, index=True ),
  155. Column( "create_time", DateTime, default=now ),
  156. Column( "update_time", DateTime, default=now, onupdate=now ),
  157. Column( "name", TEXT ),
  158. Column( "description", TEXT ),
  159. Column( "order_id", Integer ),
  160. Column( "item_count", Integer ),
  161. Column( "deleted", Boolean, index=True, default=False ),
  162. Column( "purged", Boolean, index=True, default=False ),
  163. Column( "genome_build", TrimmedString( 40 ) ) )
  164. LibraryItemInfoTemplateElement_table = Table( "library_item_info_template_element", metadata,
  165. Column( "id", Integer, primary_key=True ),
  166. Column( "create_time", DateTime, default=now ),
  167. Column( "update_time", DateTime, default=now, onupdate=now ),
  168. Column( "optional", Boolean, index=True, default=True ),
  169. Column( "deleted", Boolean, index=True, default=False ),
  170. Column( "name", TEXT ),
  171. Column( "description", TEXT ),
  172. Column( "type", TEXT, default='string' ),
  173. Column( "order_id", Integer ),
  174. Column( "options", JSONType() ),
  175. Column( "library_item_info_template_id", Integer, ForeignKey( "library_item_info_template.id" ) ) )
  176. Index( "ix_liite_library_item_info_template_id", LibraryItemInfoTemplateElement_table.c.library_item_info_template_id )
  177. LibraryItemInfoTemplate_table = Table( "library_item_info_template", metadata,
  178. Column( "id", Integer, primary_key=True ),
  179. Column( "create_time", DateTime, default=now ),
  180. Column( "update_time", DateTime, default=now, onupdate=now ),
  181. Column( "optional", Boolean, index=True, default=True ),
  182. Column( "deleted", Boolean, index=True, default=False ),
  183. Column( "name", TEXT ),
  184. Column( "description", TEXT ),
  185. Column( "item_count", Integer, default=0 ) )
  186. LibraryInfoTemplateAssociation_table = Table( "library_info_template_association", metadata,
  187. Column( "id", Integer, primary_key=True ),
  188. Column( "create_time", DateTime, default=now ),
  189. Column( "update_time", DateTime, default=now, onupdate=now ),
  190. Column( "library_id", Integer, ForeignKey( "library.id" ), nullable=True, index=True ),
  191. Column( "library_item_info_template_id", Integer, ForeignKey( "library_item_info_template.id" ) ) )
  192. Index( "ix_lita_library_item_info_template_id", LibraryInfoTemplateAssociation_table.c.library_item_info_template_id )
  193. LibraryFolderInfoTemplateAssociation_table = Table( "library_folder_info_template_association", metadata,
  194. Column( "id", Integer, primary_key=True ),
  195. Column( "create_time", DateTime, default=now ),
  196. Column( "update_time", DateTime, default=now, onupdate=now ),
  197. Column( "library_folder_id", Integer, ForeignKey( "library_folder.id" ), nullable=True, index=True ),
  198. Column( "library_item_info_template_id", Integer, ForeignKey( "library_item_info_template.id" ) ) )
  199. Index( "ix_lfita_library_item_info_template_id", LibraryFolderInfoTemplateAssociation_table.c.library_item_info_template_id )
  200. LibraryDatasetInfoTemplateAssociation_table = Table( "library_dataset_info_template_association", metadata,
  201. Column( "id", Integer, primary_key=True ),
  202. Column( "create_time", DateTime, default=now ),
  203. Column( "update_time", DateTime, default=now, onupdate=now ),
  204. Column( "library_dataset_id", Integer, ForeignKey( "library_dataset.id" ), nullable=True, index=True ),
  205. Column( "library_item_info_template_id", Integer, ForeignKey( "library_item_info_template.id" ) ) )
  206. Index( "ix_ldita_library_item_info_template_id", LibraryDatasetInfoTemplateAssociation_table.c.library_item_info_template_id )
  207. LibraryDatasetDatasetInfoTemplateAssociation_table = Table( "library_dataset_dataset_info_template_association", metadata,
  208. Column( "id", Integer, primary_key=True ),
  209. Column( "create_time", DateTime, default=now ),
  210. Column( "update_time", DateTime, default=now, onupdate=now ),
  211. Column( "library_dataset_dataset_association_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), nullable=True ),
  212. Column( "library_item_info_template_id", Integer, ForeignKey( "library_item_info_template.id" ) ) )
  213. Index( "ix_lddita_library_dataset_dataset_association_id", LibraryDatasetDatasetInfoTemplateAssociation_table.c.library_dataset_dataset_association_id )
  214. Index( "ix_lddita_library_item_info_template_id", LibraryDatasetDatasetInfoTemplateAssociation_table.c.library_item_info_template_id )
  215. LibraryItemInfoElement_table = Table( "library_item_info_element", metadata,
  216. Column( "id", Integer, primary_key=True ),
  217. Column( "create_time", DateTime, default=now ),
  218. Column( "update_time", DateTime, default=now, onupdate=now ),
  219. Column( "contents", JSONType() ),
  220. Column( "library_item_info_id", Integer, ForeignKey( "library_item_info.id" ), index=True ),
  221. Column( "library_item_info_template_element_id", Integer, ForeignKey( "library_item_info_template_element.id" ) ) )
  222. Index( "ix_liie_library_item_info_template_element_id", LibraryItemInfoElement_table.c.library_item_info_template_element_id )
  223. LibraryItemInfo_table = Table( "library_item_info", metadata,
  224. Column( "id", Integer, primary_key=True ),
  225. Column( "create_time", DateTime, default=now ),
  226. Column( "update_time", DateTime, default=now, onupdate=now ),
  227. Column( "deleted", Boolean, index=True, default=False ),
  228. Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), nullable=True, index=True ),
  229. Column( "library_item_info_template_id", Integer, ForeignKey( "library_item_info_template.id" ), nullable=True, index=True ) )
  230. LibraryInfoAssociation_table = Table( "library_info_association", metadata,
  231. Column( "id", Integer, primary_key=True ),
  232. Column( "create_time", DateTime, default=now ),
  233. Column( "update_time", DateTime, default=now, onupdate=now ),
  234. Column( "library_id", Integer, ForeignKey( "library.id" ), nullable=True, index=True ),
  235. Column( "library_item_info_id", Integer, ForeignKey( "library_item_info.id" ), index=True ),
  236. Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), nullable=True, index=True ) )
  237. LibraryFolderInfoAssociation_table = Table( "library_folder_info_association", metadata,
  238. Column( "id", Integer, primary_key=True ),
  239. Column( "create_time", DateTime, default=now ),
  240. Column( "update_time", DateTime, default=now, onupdate=now ),
  241. Column( "library_folder_id", Integer, ForeignKey( "library_folder.id" ), nullable=True, index=True ),
  242. Column( "library_item_info_id", Integer, ForeignKey( "library_item_info.id" ), index=True ),
  243. Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), nullable=True, index=True ) )
  244. LibraryDatasetInfoAssociation_table = Table( "library_dataset_info_association", metadata,
  245. Column( "id", Integer, primary_key=True ),
  246. Column( "create_time", DateTime, default=now ),
  247. Column( "update_time", DateTime, default=now, onupdate=now ),
  248. Column( "library_dataset_id", Integer, ForeignKey( "library_dataset.id" ), nullable=True, index=True ),
  249. Column( "library_item_info_id", Integer, ForeignKey( "library_item_info.id" ), index=True ),
  250. Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), nullable=True, index=True ) )
  251. LibraryDatasetDatasetInfoAssociation_table = Table( "library_dataset_dataset_info_association", metadata,
  252. Column( "id", Integer, primary_key=True ),
  253. Column( "create_time", DateTime, default=now ),
  254. Column( "update_time", DateTime, default=now, onupdate=now ),
  255. Column( "library_dataset_dataset_association_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), nullable=True ),
  256. Column( "library_item_info_id", Integer, ForeignKey( "library_item_info.id" ) ),
  257. Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), nullable=True, index=True ) )
  258. Index( "ix_lddia_library_dataset_dataset_association_id", LibraryDatasetDatasetInfoAssociation_table.c.library_dataset_dataset_association_id )
  259. Index( "ix_lddia_library_item_info_id", LibraryDatasetDatasetInfoAssociation_table.c.library_item_info_id )
  260. JobExternalOutputMetadata_table = Table( "job_external_output_metadata", metadata,
  261. Column( "id", Integer, primary_key=True ),
  262. Column( "job_id", Integer, ForeignKey( "job.id" ), index=True ),
  263. Column( "history_dataset_association_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True, nullable=True ),
  264. Column( "library_dataset_dataset_association_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), nullable=True ),
  265. Column( "filename_in", String( 255 ) ),
  266. Column( "filename_out", String( 255 ) ),
  267. Column( "filename_results_code", String( 255 ) ),
  268. Column( "filename_kwds", String( 255 ) ),
  269. Column( "job_runner_external_pid", String( 255 ) ) )
  270. Index( "ix_jeom_library_dataset_dataset_association_id", JobExternalOutputMetadata_table.c.library_dataset_dataset_association_id )
  271. def upgrade(migrate_engine):
  272. db_session = scoped_session( sessionmaker( bind=migrate_engine, autoflush=False, autocommit=True ) )
  273. metadata.bind = migrate_engine
  274. # Load existing tables
  275. metadata.reflect()
  276. def nextval( table, col='id' ):
  277. if migrate_engine.name == 'postgres':
  278. return "nextval('%s_%s_seq')" % ( table, col )
  279. elif migrate_engine.name == 'mysql' or migrate_engine.name == 'sqlite':
  280. return "null"
  281. else:
  282. raise Exception( 'Unable to convert data for unknown database type: %s' % migrate_engine.name )
  283. def localtimestamp():
  284. if migrate_engine.name == 'postgres' or migrate_engine.name == 'mysql':
  285. return "LOCALTIMESTAMP"
  286. elif migrate_engine.name == 'sqlite':
  287. return "current_date || ' ' || current_time"
  288. else:
  289. raise Exception( 'Unable to convert data for unknown database type: %s' % db )
  290. def boolean_false():
  291. if migrate_engine.name == 'postgres' or migrate_engine.name == 'mysql':
  292. return False
  293. elif migrate_engine.name == 'sqlite':
  294. return 0
  295. else:
  296. raise Exception( 'Unable to convert data for unknown database type: %s' % db )
  297. # Add 2 new columns to the galaxy_user table
  298. try:
  299. User_table = Table( "galaxy_user", metadata, autoload=True )
  300. except NoSuchTableError:
  301. User_table = None
  302. log.debug( "Failed loading table galaxy_user" )
  303. if User_table is not None:
  304. try:
  305. col = Column( 'deleted', Boolean, index=True, default=False )
  306. col.create( User_table, index_name='ix_user_deleted')
  307. assert col is User_table.c.deleted
  308. except Exception, e:
  309. log.debug( "Adding column 'deleted' to galaxy_user table failed: %s" % ( str( e ) ) )
  310. try:
  311. col = Column( 'purged', Boolean, index=True, default=False )
  312. col.create( User_table, index_name='ix_user_purged')
  313. assert col is User_table.c.purged
  314. except Exception, e:
  315. log.debug( "Adding column 'purged' to galaxy_user table failed: %s" % ( str( e ) ) )
  316. # Add 1 new column to the history_dataset_association table
  317. try:
  318. HistoryDatasetAssociation_table = Table( "history_dataset_association", metadata, autoload=True )
  319. except NoSuchTableError:
  320. HistoryDatasetAssociation_table = None
  321. log.debug( "Failed loading table history_dataset_association" )
  322. if HistoryDatasetAssociation_table is not None:
  323. try:
  324. col = Column( 'copied_from_library_dataset_dataset_association_id', Integer, nullable=True )
  325. col.create( HistoryDatasetAssociation_table)
  326. assert col is HistoryDatasetAssociation_table.c.copied_from_library_dataset_dataset_association_id
  327. except Exception, e:
  328. log.debug( "Adding column 'copied_from_library_dataset_dataset_association_id' to history_dataset_association table failed: %s" % ( str( e ) ) )
  329. # Add 1 new column to the metadata_file table
  330. try:
  331. MetadataFile_table = Table( "metadata_file", metadata, autoload=True )
  332. except NoSuchTableError:
  333. MetadataFile_table = None
  334. log.debug( "Failed loading table metadata_file" )
  335. if MetadataFile_table is not None:
  336. try:
  337. col = Column( 'lda_id', Integer, index=True, nullable=True )
  338. col.create( MetadataFile_table, index_name='ix_metadata_file_lda_id')
  339. assert col is MetadataFile_table.c.lda_id
  340. except Exception, e:
  341. log.debug( "Adding column 'lda_id' to metadata_file table failed: %s" % ( str( e ) ) )
  342. # Add 1 new column to the stored_workflow table - changeset 2328
  343. try:
  344. StoredWorkflow_table = Table( "stored_workflow", metadata,
  345. Column( "latest_workflow_id", Integer,
  346. ForeignKey( "workflow.id", use_alter=True, name='stored_workflow_latest_workflow_id_fk' ), index=True ),
  347. autoload=True, extend_existing=True )
  348. except NoSuchTableError:
  349. StoredWorkflow_table = None
  350. log.debug( "Failed loading table stored_workflow" )
  351. if StoredWorkflow_table is not None:
  352. try:
  353. col = Column( 'importable', Boolean, default=False )
  354. col.create( StoredWorkflow_table )
  355. assert col is StoredWorkflow_table.c.importable
  356. except Exception, e:
  357. log.debug( "Adding column 'importable' to stored_workflow table failed: %s" % ( str( e ) ) )
  358. # Create an index on the Job.state column - changeset 2192
  359. try:
  360. Job_table = Table( "job", metadata, autoload=True )
  361. except NoSuchTableError:
  362. Job_table = None
  363. log.debug( "Failed loading table job" )
  364. if Job_table is not None:
  365. try:
  366. i = Index( 'ix_job_state', Job_table.c.state )
  367. i.create()
  368. except Exception, e:
  369. log.debug( "Adding index to job.state column failed: %s" % ( str( e ) ) )
  370. # Add all of the new tables above
  371. metadata.create_all()
  372. # Add 1 foreign key constraint to the history_dataset_association table
  373. try:
  374. HistoryDatasetAssociation_table = Table( "history_dataset_association", metadata, autoload=True )
  375. except NoSuchTableError:
  376. HistoryDatasetAssociation_table = None
  377. log.debug( "Failed loading table history_dataset_association" )
  378. try:
  379. LibraryDatasetDatasetAssociation_table = Table( "library_dataset_dataset_association", metadata, autoload=True )
  380. except NoSuchTableError:
  381. LibraryDatasetDatasetAssociation_table = None
  382. log.debug( "Failed loading table library_dataset_dataset_association" )
  383. if HistoryDatasetAssociation_table is not None and LibraryDatasetDatasetAssociation_table is not None:
  384. try:
  385. cons = ForeignKeyConstraint( [HistoryDatasetAssociation_table.c.copied_from_library_dataset_dataset_association_id],
  386. [LibraryDatasetDatasetAssociation_table.c.id],
  387. name='history_dataset_association_copied_from_library_dataset_da_fkey' )
  388. # Create the constraint
  389. cons.create()
  390. except Exception, e:
  391. log.debug( "Adding foreign key constraint 'history_dataset_association_copied_from_library_dataset_da_fkey' to table 'history_dataset_association' failed: %s" % ( str( e ) ) )
  392. # Add 1 foreign key constraint to the metadata_file table
  393. try:
  394. MetadataFile_table = Table( "metadata_file", metadata, autoload=True )
  395. except NoSuchTableError:
  396. MetadataFile_table = None
  397. log.debug( "Failed loading table metadata_file" )
  398. try:
  399. LibraryDatasetDatasetAssociation_table = Table( "library_dataset_dataset_association", metadata, autoload=True )
  400. except NoSuchTableError:
  401. LibraryDatasetDatasetAssociation_table = None
  402. log.debug( "Failed loading table library_dataset_dataset_association" )
  403. if migrate_engine.name != 'sqlite':
  404. #Sqlite can't alter table add foreign key.
  405. if MetadataFile_table is not None and LibraryDatasetDatasetAssociation_table is not None:
  406. try:
  407. cons = ForeignKeyConstraint( [MetadataFile_table.c.lda_id],
  408. [LibraryDatasetDatasetAssociation_table.c.id],
  409. name='metadata_file_lda_id_fkey' )
  410. # Create the constraint
  411. cons.create()
  412. except Exception, e:
  413. log.debug( "Adding foreign key constraint 'metadata_file_lda_id_fkey' to table 'metadata_file' failed: %s" % ( str( e ) ) )
  414. # Make sure we have at least 1 user
  415. cmd = "SELECT * FROM galaxy_user;"
  416. users = db_session.execute( cmd ).fetchall()
  417. if users:
  418. cmd = "SELECT * FROM role;"
  419. roles = db_session.execute( cmd ).fetchall()
  420. if not roles:
  421. # Create private roles for each user - pass 1
  422. cmd = \
  423. "INSERT INTO role " + \
  424. "SELECT %s AS id," + \
  425. "%s AS create_time," + \
  426. "%s AS update_time," + \
  427. "email AS name," + \
  428. "email AS description," + \
  429. "'private' As type," + \
  430. "%s AS deleted " + \
  431. "FROM galaxy_user " + \
  432. "ORDER BY id;"
  433. cmd = cmd % ( nextval('role'), localtimestamp(), localtimestamp(), boolean_false() )
  434. db_session.execute( cmd )
  435. # Create private roles for each user - pass 2
  436. if migrate_engine.name == 'postgres' or migrate_engine.name == 'sqlite':
  437. cmd = "UPDATE role SET description = 'Private role for ' || description;"
  438. elif migrate_engine.name == 'mysql':
  439. cmd = "UPDATE role SET description = CONCAT( 'Private role for ', description );"
  440. db_session.execute( cmd )
  441. # Create private roles for each user - pass 3
  442. cmd = \
  443. "INSERT INTO user_role_association " + \
  444. "SELECT %s AS id," + \
  445. "galaxy_user.id AS user_id," + \
  446. "role.id AS role_id," + \
  447. "%s AS create_time," + \
  448. "%s AS update_time " + \
  449. "FROM galaxy_user, role " + \
  450. "WHERE galaxy_user.email = role.name " + \
  451. "ORDER BY galaxy_user.id;"
  452. cmd = cmd % ( nextval('user_role_association'), localtimestamp(), localtimestamp() )
  453. db_session.execute( cmd )
  454. # Create default permissions for each user
  455. cmd = \
  456. "INSERT INTO default_user_permissions " + \
  457. "SELECT %s AS id," + \
  458. "galaxy_user.id AS user_id," + \
  459. "'manage permissions' AS action," + \
  460. "user_role_association.role_id AS role_id " + \
  461. "FROM galaxy_user " + \
  462. "JOIN user_role_association ON user_role_association.user_id = galaxy_user.id " + \
  463. "ORDER BY galaxy_user.id;"
  464. cmd = cmd % nextval('default_user_permissions')
  465. db_session.execute( cmd )
  466. # Create default history permissions for each active history associated with a user
  467. cmd = \
  468. "INSERT INTO default_history_permissions " + \
  469. "SELECT %s AS id," + \
  470. "history.id AS history_id," + \
  471. "'manage permissions' AS action," + \
  472. "user_role_association.role_id AS role_id " + \
  473. "FROM history " + \
  474. "JOIN user_role_association ON user_role_association.user_id = history.user_id " + \
  475. "WHERE history.purged = %s AND history.user_id IS NOT NULL;"
  476. cmd = cmd % ( nextval('default_history_permissions'), boolean_false() )
  477. db_session.execute( cmd )
  478. # Create "manage permissions" dataset_permissions for all activate-able datasets
  479. cmd = \
  480. "INSERT INTO dataset_permissions " + \
  481. "SELECT %s AS id," + \
  482. "%s AS create_time," + \
  483. "%s AS update_time," + \
  484. "'manage permissions' AS action," + \
  485. "history_dataset_association.dataset_id AS dataset_id," + \
  486. "user_role_association.role_id AS role_id " + \
  487. "FROM history " + \
  488. "JOIN history_dataset_association ON history_dataset_association.history_id = history.id " + \
  489. "JOIN dataset ON history_dataset_association.dataset_id = dataset.id " + \
  490. "JOIN user_role_association ON user_role_association.user_id = history.user_id " + \
  491. "WHERE dataset.purged = %s AND history.user_id IS NOT NULL;"
  492. cmd = cmd % ( nextval('dataset_permissions'), localtimestamp(), localtimestamp(), boolean_false() )
  493. db_session.execute( cmd )
  494. def downgrade(migrate_engine):
  495. metadata.bind = migrate_engine
  496. if migrate_engine.name == 'postgres':
  497. # http://blog.pythonisito.com/2008/01/cascading-drop-table-with-sqlalchemy.html
  498. from sqlalchemy.databases import postgres
  499. class PGCascadeSchemaDropper(postgres.PGSchemaDropper):
  500. def visit_table(self, table):
  501. for column in table.columns:
  502. if column.default is not None:
  503. self.traverse_single(column.default)
  504. self.append("\nDROP TABLE " +
  505. self.preparer.format_table(table) +
  506. " CASCADE")
  507. self.execute()
  508. postgres.dialect.schemadropper = PGCascadeSchemaDropper
  509. # Load existing tables
  510. metadata.reflect()
  511. # NOTE: all new data added in the upgrade method is eliminated here via table drops
  512. # Drop 1 foreign key constraint from the metadata_file table
  513. try:
  514. MetadataFile_table = Table( "metadata_file", metadata, autoload=True )
  515. except NoSuchTableError:
  516. MetadataFile_table = None
  517. log.debug( "Failed loading table metadata_file" )
  518. try:
  519. LibraryDatasetDatasetAssociation_table = Table( "library_dataset_dataset_association", metadata, autoload=True )
  520. except NoSuchTableError:
  521. LibraryDatasetDatasetAssociation_table = None
  522. log.debug( "Failed loading table library_dataset_dataset_association" )
  523. if MetadataFile_table is not None and LibraryDatasetDatasetAssociation_table is not None:
  524. try:
  525. cons = ForeignKeyConstraint( [MetadataFile_table.c.lda_id],
  526. [LibraryDatasetDatasetAssociation_table.c.id],
  527. name='metadata_file_lda_id_fkey' )
  528. # Drop the constraint
  529. cons.drop()
  530. except Exception, e:
  531. log.debug( "Dropping foreign key constraint 'metadata_file_lda_id_fkey' from table 'metadata_file' failed: %s" % ( str( e ) ) )
  532. # Drop 1 foreign key constraint from the history_dataset_association table
  533. try:
  534. HistoryDatasetAssociation_table = Table( "history_dataset_association", metadata, autoload=True )
  535. except NoSuchTableError:
  536. HistoryDatasetAssociation_table = None
  537. log.debug( "Failed loading table history_dataset_association" )
  538. try:
  539. LibraryDatasetDatasetAssociation_table = Table( "library_dataset_dataset_association", metadata, autoload=True )
  540. except NoSuchTableError:
  541. LibraryDatasetDatasetAssociation_table = None
  542. log.debug( "Failed loading table library_dataset_dataset_association" )
  543. if HistoryDatasetAssociation_table is not None and LibraryDatasetDatasetAssociation_table is not None:
  544. try:
  545. cons = ForeignKeyConstraint( [HistoryDatasetAssociation_table.c.copied_from_library_dataset_dataset_association_id],
  546. [LibraryDatasetDatasetAssociation_table.c.id],
  547. name='history_dataset_association_copied_from_library_dataset_da_fkey' )
  548. # Drop the constraint
  549. cons.drop()
  550. except Exception, e:
  551. log.debug( "Dropping foreign key constraint 'history_dataset_association_copied_from_library_dataset_da_fkey' from table 'history_dataset_association' failed: %s" % ( str( e ) ) )
  552. # Drop all of the new tables above
  553. try:
  554. UserGroupAssociation_table.drop()
  555. except Exception, e:
  556. log.debug( "Dropping user_group_association table failed: %s" % str( e ) )
  557. try:
  558. UserRoleAssociation_table.drop()
  559. except Exception, e:
  560. log.debug( "Dropping user_role_association table failed: %s" % str( e ) )
  561. try:
  562. GroupRoleAssociation_table.drop()
  563. except Exception, e:
  564. log.debug( "Dropping group_role_association table failed: %s" % str( e ) )
  565. try:
  566. Group_table.drop()
  567. except Exception, e:
  568. log.debug( "Dropping galaxy_group table failed: %s" % str( e ) )
  569. try:
  570. DatasetPermissions_table.drop()
  571. except Exception, e:
  572. log.debug( "Dropping dataset_permissions table failed: %s" % str( e ) )
  573. try:
  574. LibraryPermissions_table.drop()
  575. except Exception, e:
  576. log.debug( "Dropping library_permissions table failed: %s" % str( e ) )
  577. try:
  578. LibraryFolderPermissions_table.drop()
  579. except Exception, e:
  580. log.debug( "Dropping library_folder_permissions table failed: %s" % str( e ) )
  581. try:
  582. LibraryDatasetPermissions_table.drop()
  583. except Exception, e:
  584. log.debug( "Dropping library_dataset_permissions table failed: %s" % str( e ) )
  585. try:
  586. LibraryDatasetDatasetAssociationPermissions_table.drop()
  587. except Exception, e:
  588. log.debug( "Dropping library_dataset_dataset_association_permissions table failed: %s" % str( e ) )
  589. try:
  590. LibraryItemInfoPermissions_table.drop()
  591. except Exception, e:
  592. log.debug( "Dropping library_item_info_permissions table failed: %s" % str( e ) )
  593. try:
  594. LibraryItemInfoTemplatePermissions_table.drop()
  595. except Exception, e:
  596. log.debug( "Dropping library_item_info_template_permissions table failed: %s" % str( e ) )
  597. try:
  598. DefaultUserPermissions_table.drop()
  599. except Exception, e:
  600. log.debug( "Dropping default_user_permissions table failed: %s" % str( e ) )
  601. try:
  602. DefaultHistoryPermissions_table.drop()
  603. except Exception, e:
  604. log.debug( "Dropping default_history_permissions table failed: %s" % str( e ) )
  605. try:
  606. Role_table.drop()
  607. except Exception, e:
  608. log.debug( "Dropping role table failed: %s" % str( e ) )
  609. try:
  610. LibraryDatasetDatasetInfoAssociation_table.drop()
  611. except Exception, e:
  612. log.debug( "Dropping library_dataset_dataset_info_association table failed: %s" % str( e ) )
  613. try:
  614. LibraryDataset_table.drop()
  615. except Exception, e:
  616. log.debug( "Dropping library_dataset table failed: %s" % str( e ) )
  617. try:
  618. LibraryDatasetDatasetAssociation_table.drop()
  619. except Exception, e:
  620. log.debug( "Dropping library_dataset_dataset_association table failed: %s" % str( e ) )
  621. try:
  622. LibraryDatasetDatasetInfoTemplateAssociation_table.drop()
  623. except Exception, e:
  624. log.debug( "Dropping library_dataset_dataset_info_template_association table failed: %s" % str( e ) )
  625. try:
  626. JobExternalOutputMetadata_table.drop()
  627. except Exception, e:
  628. log.debug( "Dropping job_external_output_metadata table failed: %s" % str( e ) )
  629. try:
  630. Library_table.drop()
  631. except Exception, e:
  632. log.debug( "Dropping library table failed: %s" % str( e ) )
  633. try:
  634. LibraryFolder_table.drop()
  635. except Exception, e:
  636. log.debug( "Dropping library_folder table failed: %s" % str( e ) )
  637. try:
  638. LibraryItemInfoTemplateElement_table.drop()
  639. except Exception, e:
  640. log.debug( "Dropping library_item_info_template_element table failed: %s" % str( e ) )
  641. try:
  642. LibraryInfoTemplateAssociation_table.drop()
  643. except Exception, e:
  644. log.debug( "Dropping library_info_template_association table failed: %s" % str( e ) )
  645. try:
  646. LibraryFolderInfoTemplateAssociation_table.drop()
  647. except Exception, e:
  648. log.debug( "Dropping library_folder_info_template_association table failed: %s" % str( e ) )
  649. try:
  650. LibraryDatasetInfoTemplateAssociation_table.drop()
  651. except Exception, e:
  652. log.debug( "Dropping library_dataset_info_template_association table failed: %s" % str( e ) )
  653. try:
  654. LibraryInfoAssociation_table.drop()
  655. except Exception, e:
  656. log.debug( "Dropping library_info_association table failed: %s" % str( e ) )
  657. try:
  658. LibraryFolderInfoAssociation_table.drop()
  659. except Exception, e:
  660. log.debug( "Dropping library_folder_info_association table failed: %s" % str( e ) )
  661. try:
  662. LibraryDatasetInfoAssociation_table.drop()
  663. except Exception, e:
  664. log.debug( "Dropping library_dataset_info_association table failed: %s" % str( e ) )
  665. try:
  666. LibraryItemInfoElement_table.drop()
  667. except Exception, e:
  668. log.debug( "Dropping library_item_info_element table failed: %s" % str( e ) )
  669. try:
  670. LibraryItemInfo_table.drop()
  671. except Exception, e:
  672. log.debug( "Dropping library_item_info table failed: %s" % str( e ) )
  673. try:
  674. LibraryItemInfoTemplate_table.drop()
  675. except Exception, e:
  676. log.debug( "Dropping library_item_info_template table failed: %s" % str( e ) )
  677. # Drop the index on the Job.state column - changeset 2192
  678. try:
  679. Job_table = Table( "job", metadata, autoload=True )
  680. except NoSuchTableError:
  681. Job_table = None
  682. log.debug( "Failed loading table job" )
  683. if Job_table is not None:
  684. try:
  685. i = Index( 'ix_job_state', Job_table.c.state )
  686. i.drop()
  687. except Exception, e:
  688. log.debug( "Dropping index from job.state column failed: %s" % ( str( e ) ) )
  689. # Drop 1 column from the stored_workflow table - changeset 2328
  690. try:
  691. StoredWorkflow_table = Table( "stored_workflow", metadata, autoload=True )
  692. except NoSuchTableError:
  693. StoredWorkflow_table = None
  694. log.debug( "Failed loading table stored_workflow" )
  695. if StoredWorkflow_table is not None:
  696. try:
  697. col = StoredWorkflow_table.c.importable
  698. col.drop()
  699. except Exception, e:
  700. log.debug( "Dropping column 'importable' from stored_workflow table failed: %s" % ( str( e ) ) )
  701. # Drop 1 column from the metadata_file table
  702. try:
  703. MetadataFile_table = Table( "metadata_file", metadata, autoload=True )
  704. except NoSuchTableError:
  705. MetadataFile_table = None
  706. log.debug( "Failed loading table metadata_file" )
  707. if MetadataFile_table is not None:
  708. try:
  709. col = MetadataFile_table.c.lda_id
  710. col.drop()
  711. except Exception, e:
  712. log.debug( "Dropping column 'lda_id' from metadata_file table failed: %s" % ( str( e ) ) )
  713. # Drop 1 column from the history_dataset_association table
  714. try:
  715. HistoryDatasetAssociation_table = Table( "history_dataset_association", metadata, autoload=True )
  716. except NoSuchTableError:
  717. HistoryDatasetAssociation_table = None
  718. log.debug( "Failed loading table history_dataset_association" )
  719. if HistoryDatasetAssociation_table is not None:
  720. try:
  721. col = HistoryDatasetAssociation_table.c.copied_from_library_dataset_dataset_association_id
  722. col.drop()
  723. except Exception, e:
  724. log.debug( "Dropping column 'copied_from_library_dataset_dataset_association_id' from history_dataset_association table failed: %s" % ( str( e ) ) )
  725. # Drop 2 columns from the galaxy_user table
  726. try:
  727. User_table = Table( "galaxy_user", metadata, autoload=True )
  728. except NoSuchTableError:
  729. User_table = None
  730. log.debug( "Failed loading table galaxy_user" )
  731. if User_table is not None:
  732. try:
  733. col = User_table.c.deleted
  734. col.drop()
  735. except Exception, e:
  736. log.debug( "Dropping column 'deleted' from galaxy_user table failed: %s" % ( str( e ) ) )
  737. try:
  738. col = User_table.c.purged
  739. col.drop()
  740. except Exception, e:
  741. log.debug( "Dropping column 'purged' from galaxy_user table failed: %s" % ( str( e ) ) )