1. hiralv
  2. galaxy-central

Source

galaxy-central / lib / galaxy / model / mapping.py

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782
1783
1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845
1846
1847
1848
1849
1850
1851
1852
1853
1854
1855
1856
1857
1858
1859
1860
1861
1862
1863
1864
1865
1866
1867
1868
1869
"""
Details of how the data model objects are mapped onto the relational database
are encapsulated here. 
"""
import logging
log = logging.getLogger( __name__ )

import sys
import datetime

from galaxy.model import *
from galaxy.model.orm import *
from galaxy.model.orm.ext.assignmapper import *
from galaxy.model.custom_types import *
from galaxy.util.bunch import Bunch
from galaxy.security import GalaxyRBACAgent
from sqlalchemy.orm.collections import attribute_mapped_collection
from sqlalchemy.ext.associationproxy import association_proxy

metadata = MetaData()
context = Session = scoped_session( sessionmaker( autoflush=False, autocommit=True ) )

# For backward compatibility with "context.current"
context.current = Session

dialect_to_egg = { 
    "sqlite"   : "pysqlite>=2",
    "postgres" : "psycopg2",
    "mysql"    : "MySQL_python"
}

# NOTE REGARDING TIMESTAMPS:
#   It is currently difficult to have the timestamps calculated by the 
#   database in a portable way, so we're doing it in the client. This
#   also saves us from needing to postfetch on postgres. HOWEVER: it
#   relies on the client's clock being set correctly, so if clustering
#   web servers, use a time server to ensure synchronization

# Return the current time in UTC without any timezone information
now = datetime.datetime.utcnow

User.table = Table( "galaxy_user", metadata,
    Column( "id", Integer, primary_key=True),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "email", TrimmedString( 255 ), nullable=False ),
    Column( "username", TrimmedString( 255 ), index=True, unique=True ),
    Column( "password", TrimmedString( 40 ), nullable=False ),
    Column( "external", Boolean, default=False ),
    Column( "form_values_id", Integer, ForeignKey( "form_values.id" ), index=True ),
    Column( "deleted", Boolean, index=True, default=False ),
    Column( "purged", Boolean, index=True, default=False ),
    Column( "disk_usage", Numeric( 15, 0 ), index=True ) )

UserAddress.table = Table( "user_address", metadata,
    Column( "id", Integer, primary_key=True),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
    Column( "desc", TrimmedString( 255 )),    
    Column( "name", TrimmedString( 255 ), nullable=False),
    Column( "institution", TrimmedString( 255 )),
    Column( "address", TrimmedString( 255 ), nullable=False),
    Column( "city", TrimmedString( 255 ), nullable=False),
    Column( "state", TrimmedString( 255 ), nullable=False),
    Column( "postal_code", TrimmedString( 255 ), nullable=False),
    Column( "country", TrimmedString( 255 ), nullable=False),
    Column( "phone", TrimmedString( 255 )),
    Column( "deleted", Boolean, index=True, default=False ),
    Column( "purged", Boolean, index=True, default=False ) )

UserOpenID.table = Table( "galaxy_user_openid", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, index=True, default=now, onupdate=now ),
    Column( "session_id", Integer, ForeignKey( "galaxy_session.id" ), index=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
    Column( "openid", TEXT, index=True, unique=True ),
    Column( "provider", TrimmedString( 255 ) ),
    )

History.table = Table( "history", metadata,
    Column( "id", Integer, primary_key=True),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, index=True, default=now, onupdate=now ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
    Column( "name", TrimmedString( 255 ) ),
    Column( "hid_counter", Integer, default=1 ),
    Column( "deleted", Boolean, index=True, default=False ),
    Column( "purged", Boolean, index=True, default=False ),
    Column( "importing", Boolean, index=True, default=False ),
    Column( "genome_build", TrimmedString( 40 ) ),
    Column( "importable", Boolean, default=False ),
    Column( "slug", TEXT, index=True ),
    Column( "published", Boolean, index=True, default=False ) )

HistoryUserShareAssociation.table = Table( "history_user_share_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True )
    )

HistoryDatasetAssociation.table = Table( "history_dataset_association", metadata, 
    Column( "id", Integer, primary_key=True ),
    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True ),
    Column( "dataset_id", Integer, ForeignKey( "dataset.id" ), index=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "state", TrimmedString( 64 ), index=True, key="_state" ),
    Column( "copied_from_history_dataset_association_id", Integer, ForeignKey( "history_dataset_association.id" ), nullable=True ),
    Column( "copied_from_library_dataset_dataset_association_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), nullable=True ),
    Column( "hid", Integer ),
    Column( "name", TrimmedString( 255 ) ),
    Column( "info", TrimmedString( 255 ) ),
    Column( "blurb", TrimmedString( 255 ) ),
    Column( "peek" , TEXT ),
    Column( "tool_version" , TEXT ),
    Column( "extension", TrimmedString( 64 ) ),
    Column( "metadata", MetadataType(), key="_metadata" ),
    Column( "parent_id", Integer, ForeignKey( "history_dataset_association.id" ), nullable=True ),
    Column( "designation", TrimmedString( 255 ) ),
    Column( "deleted", Boolean, index=True, default=False ),
    Column( "purged", Boolean, index=True, default=False ),
    Column( "visible", Boolean ) )

Dataset.table = Table( "dataset", metadata, 
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, index=True, default=now, onupdate=now ),
    Column( "state", TrimmedString( 64 ), index=True ),
    Column( "deleted", Boolean, index=True, default=False ),
    Column( "purged", Boolean, index=True, default=False ),
    Column( "purgable", Boolean, default=True ),
    Column( "object_store_id", TrimmedString( 255 ), index=True ),
    Column( "external_filename" , TEXT ),
    Column( "_extra_files_path", TEXT ),
    Column( 'file_size', Numeric( 15, 0 ) ),
    Column( 'total_size', Numeric( 15, 0 ) ) )

HistoryDatasetAssociationDisplayAtAuthorization.table = Table( "history_dataset_association_display_at_authorization", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, index=True, default=now, onupdate=now ),
    Column( "history_dataset_association_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
    Column( "site", TrimmedString( 255 ) ) )
    
HistoryDatasetAssociationSubset.table = Table( "history_dataset_association_subset", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "history_dataset_association_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True ),
    Column( "history_dataset_association_subset_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True ),
    Column( "location", Unicode(255), index=True) )

ImplicitlyConvertedDatasetAssociation.table = Table( "implicitly_converted_dataset_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "hda_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True, nullable=True ),
    Column( "ldda_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), index=True, nullable=True ),
    Column( "hda_parent_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True ),
    Column( "ldda_parent_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), index=True ),
    Column( "deleted", Boolean, index=True, default=False ),
    Column( "metadata_safe", Boolean, index=True, default=True ),
    Column( "type", TrimmedString( 255 ) ) )

ValidationError.table = Table( "validation_error", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "dataset_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True ),
    Column( "message", TrimmedString( 255 ) ),
    Column( "err_type", TrimmedString( 64 ) ),
    Column( "attributes", TEXT ) )

Group.table = Table( "galaxy_group", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "name", String( 255 ), index=True, unique=True ),
    Column( "deleted", Boolean, index=True, default=False ) )

UserGroupAssociation.table = Table( "user_group_association", metadata, 
    Column( "id", Integer, primary_key=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
    Column( "group_id", Integer, ForeignKey( "galaxy_group.id" ), index=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ) )

UserRoleAssociation.table = Table( "user_role_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ) )

GroupRoleAssociation.table = Table( "group_role_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "group_id", Integer, ForeignKey( "galaxy_group.id" ), index=True ),
    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ) )

Role.table = Table( "role", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "name", String( 255 ), index=True, unique=True ),
    Column( "description", TEXT ),
    Column( "type", String( 40 ), index=True ),
    Column( "deleted", Boolean, index=True, default=False ) )

UserQuotaAssociation.table = Table( "user_quota_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
    Column( "quota_id", Integer, ForeignKey( "quota.id" ), index=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ) )

GroupQuotaAssociation.table = Table( "group_quota_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "group_id", Integer, ForeignKey( "galaxy_group.id" ), index=True ),
    Column( "quota_id", Integer, ForeignKey( "quota.id" ), index=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ) )

Quota.table = Table( "quota", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "name", String( 255 ), index=True, unique=True ),
    Column( "description", TEXT ),
    Column( "bytes", BigInteger ),
    Column( "operation", String( 8 ) ),
    Column( "deleted", Boolean, index=True, default=False ) )

DefaultQuotaAssociation.table = Table( "default_quota_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "type", String( 32 ), index=True, unique=True ),
    Column( "quota_id", Integer, ForeignKey( "quota.id" ), index=True ) )

DatasetPermissions.table = Table( "dataset_permissions", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "action", TEXT ),
    Column( "dataset_id", Integer, ForeignKey( "dataset.id" ), index=True ),
    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )

LibraryPermissions.table = Table( "library_permissions", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "action", TEXT ),
    Column( "library_id", Integer, ForeignKey( "library.id" ), nullable=True, index=True ),
    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )

LibraryFolderPermissions.table = Table( "library_folder_permissions", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "action", TEXT ),
    Column( "library_folder_id", Integer, ForeignKey( "library_folder.id" ), nullable=True, index=True ),
    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )

LibraryDatasetPermissions.table = Table( "library_dataset_permissions", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "action", TEXT ),
    Column( "library_dataset_id", Integer, ForeignKey( "library_dataset.id" ), nullable=True, index=True ),
    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )

LibraryDatasetDatasetAssociationPermissions.table = Table( "library_dataset_dataset_association_permissions", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "action", TEXT ),
    Column( "library_dataset_dataset_association_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), nullable=True, index=True ),
    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )

DefaultUserPermissions.table = Table( "default_user_permissions", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
    Column( "action", TEXT ),
    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )

DefaultHistoryPermissions.table = Table( "default_history_permissions", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True ),
    Column( "action", TEXT ),
    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )

LibraryDataset.table = Table( "library_dataset", metadata, 
    Column( "id", Integer, primary_key=True ),
    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
    Column( "folder_id", Integer, ForeignKey( "library_folder.id" ), index=True ),
    Column( "order_id", Integer ), #not currently being used, but for possible future use
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "name", TrimmedString( 255 ), key="_name", index=True ), #when not None/null this will supercede display in library (but not when imported into user's history?)
    Column( "info", TrimmedString( 255 ),  key="_info" ), #when not None/null this will supercede display in library (but not when imported into user's history?)
    Column( "deleted", Boolean, index=True, default=False ),
    Column( "purged", Boolean, index=True, default=False ) )

LibraryDatasetDatasetAssociation.table = Table( "library_dataset_dataset_association", metadata, 
    Column( "id", Integer, primary_key=True ),
    Column( "library_dataset_id", Integer, ForeignKey( "library_dataset.id" ), index=True ),
    Column( "dataset_id", Integer, ForeignKey( "dataset.id" ), index=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "state", TrimmedString( 64 ), index=True, key="_state" ),
    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 ),
    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 ),
    Column( "name", TrimmedString( 255 ), index=True ),
    Column( "info", TrimmedString( 255 ) ),
    Column( "blurb", TrimmedString( 255 ) ),
    Column( "peek" , TEXT ),
    Column( "tool_version" , TEXT ),
    Column( "extension", TrimmedString( 64 ) ),
    Column( "metadata", MetadataType(), key="_metadata" ),
    Column( "parent_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), nullable=True ),
    Column( "designation", TrimmedString( 255 ) ),
    Column( "deleted", Boolean, index=True, default=False ),
    Column( "visible", Boolean ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
    Column( "message", TrimmedString( 255 ) ) )

Library.table = Table( "library", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "root_folder_id", Integer, ForeignKey( "library_folder.id" ), index=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "name", String( 255 ), index=True ),
    Column( "deleted", Boolean, index=True, default=False ),
    Column( "purged", Boolean, index=True, default=False ),
    Column( "description", TEXT ),
    Column( "synopsis", TEXT ) )

LibraryFolder.table = Table( "library_folder", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "parent_id", Integer, ForeignKey( "library_folder.id" ), nullable = True, index=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "name", TEXT, index=True ),
    Column( "description", TEXT ),
    Column( "order_id", Integer ), #not currently being used, but for possible future use
    Column( "item_count", Integer ),
    Column( "deleted", Boolean, index=True, default=False ),
    Column( "purged", Boolean, index=True, default=False ),
    Column( "genome_build", TrimmedString( 40 ) ) )

LibraryInfoAssociation.table = Table( 'library_info_association', metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "library_id", Integer, ForeignKey( "library.id" ), index=True ),
    Column( "form_definition_id", Integer, ForeignKey( "form_definition.id" ), index=True ),
    Column( "form_values_id", Integer, ForeignKey( "form_values.id" ), index=True ),
    Column( "inheritable", Boolean, index=True, default=False ),
    Column( "deleted", Boolean, index=True, default=False ) )

LibraryFolderInfoAssociation.table = Table( 'library_folder_info_association', metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "library_folder_id", Integer, ForeignKey( "library_folder.id" ), nullable=True, index=True ),
    Column( "form_definition_id", Integer, ForeignKey( "form_definition.id" ), index=True ),
    Column( "form_values_id", Integer, ForeignKey( "form_values.id" ), index=True ),
    Column( "inheritable", Boolean, index=True, default=False ),
    Column( "deleted", Boolean, index=True, default=False ) )

LibraryDatasetDatasetInfoAssociation.table = Table( 'library_dataset_dataset_info_association', metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "library_dataset_dataset_association_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), nullable=True, index=True ),
    Column( "form_definition_id", Integer, ForeignKey( "form_definition.id" ), index=True ),
    Column( "form_values_id", Integer, ForeignKey( "form_values.id" ), index=True ),
    Column( "deleted", Boolean, index=True, default=False ) )

ToolShedRepository.table = Table( "tool_shed_repository", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "tool_shed", TrimmedString( 255 ), index=True ),
    Column( "name", TrimmedString( 255 ), index=True ),
    Column( "description" , TEXT ),
    Column( "owner", TrimmedString( 255 ), index=True ),
    Column( "installed_changeset_revision", TrimmedString( 255 ) ),
    Column( "changeset_revision", TrimmedString( 255 ), index=True ),
    Column( "metadata", JSONType, nullable=True ),
    Column( "includes_datatypes", Boolean, index=True, default=False ),
    Column( "update_available", Boolean, default=False ),
    Column( "deleted", Boolean, index=True, default=False ),
    Column( "uninstalled", Boolean, default=False ),
    Column( "dist_to_shed", Boolean, default=False ) )

ToolVersion.table = Table( "tool_version", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "tool_id", String( 255 ) ),
    Column( "tool_shed_repository_id", Integer, ForeignKey( "tool_shed_repository.id" ), index=True, nullable=True ) )

ToolVersionAssociation.table = Table( "tool_version_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "tool_id", Integer, ForeignKey( "tool_version.id" ), index=True, nullable=False ),
    Column( "parent_id", Integer, ForeignKey( "tool_version.id" ), index=True, nullable=False ) )

MigrateTools.table = Table( "migrate_tools", metadata,
    Column( "repository_id", TrimmedString( 255 ) ),
    Column( "repository_path", TEXT ),
    Column( "version", Integer ) )

Job.table = Table( "job", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True ),
    Column( "library_folder_id", Integer, ForeignKey( "library_folder.id" ), index=True ),
    Column( "tool_id", String( 255 ) ),
    Column( "tool_version", TEXT, default="1.0.0" ),
    Column( "state", String( 64 ), index=True ),
    Column( "info", TrimmedString( 255 ) ),
    Column( "command_line", TEXT ), 
    Column( "param_filename", String( 1024 ) ),
    Column( "runner_name", String( 255 ) ),
    Column( "stdout", TEXT ),
    Column( "stderr", TEXT ),
    Column( "traceback", TEXT ),
    Column( "session_id", Integer, ForeignKey( "galaxy_session.id" ), index=True, nullable=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True, nullable=True ),
    Column( "job_runner_name", String( 255 ) ),
    Column( "job_runner_external_id", String( 255 ) ), 
    Column( "object_store_id", TrimmedString( 255 ), index=True ),
    Column( "imported", Boolean, default=False, index=True ),
    Column( "params", TrimmedString(255), index=True ),
    Column( "handler", TrimmedString( 255 ), index=True ) )
    
JobParameter.table = Table( "job_parameter", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "job_id", Integer, ForeignKey( "job.id" ), index=True ),
    Column( "name", String(255) ),
    Column( "value", TEXT ) )
    
JobToInputDatasetAssociation.table = Table( "job_to_input_dataset", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "job_id", Integer, ForeignKey( "job.id" ), index=True ),
    Column( "dataset_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True ),
    Column( "name", String(255) ) )
    
JobToOutputDatasetAssociation.table = Table( "job_to_output_dataset", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "job_id", Integer, ForeignKey( "job.id" ), index=True ),
    Column( "dataset_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True ),
    Column( "name", String(255) ) )
    
JobToInputLibraryDatasetAssociation.table = Table( "job_to_input_library_dataset", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "job_id", Integer, ForeignKey( "job.id" ), index=True ),
    Column( "ldda_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), index=True ),
    Column( "name", String(255) ) )

JobToOutputLibraryDatasetAssociation.table = Table( "job_to_output_library_dataset", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "job_id", Integer, ForeignKey( "job.id" ), index=True ),
    Column( "ldda_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), index=True ),
    Column( "name", String(255) ) )
    
JobExternalOutputMetadata.table = Table( "job_external_output_metadata", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "job_id", Integer, ForeignKey( "job.id" ), index=True ),
    Column( "history_dataset_association_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True, nullable=True ),
    Column( "library_dataset_dataset_association_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), index=True, nullable=True ),
    Column( "filename_in", String( 255 ) ),
    Column( "filename_out", String( 255 ) ),
    Column( "filename_results_code", String( 255 ) ),
    Column( "filename_kwds", String( 255 ) ),
    Column( "filename_override_metadata", String( 255 ) ),
    Column( "job_runner_external_pid", String( 255 ) ) )
    
JobExportHistoryArchive.table = Table( "job_export_history_archive", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "job_id", Integer, ForeignKey( "job.id" ), index=True ),
    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True ),
    Column( "dataset_id", Integer, ForeignKey( "dataset.id" ), index=True ),
    Column( "compressed", Boolean, index=True, default=False ),
    Column( "history_attrs_filename", TEXT ),
    Column( "datasets_attrs_filename", TEXT ),
    Column( "jobs_attrs_filename", TEXT )
    )
    
JobImportHistoryArchive.table = Table( "job_import_history_archive", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "job_id", Integer, ForeignKey( "job.id" ), index=True ),
    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True ),
    Column( "archive_dir", TEXT )
    )

Task.table = Table( "task", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "execution_time", DateTime ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "state", String( 64 ), index=True ),
    Column( "command_line", TEXT ), 
    Column( "param_filename", String( 1024 ) ),
    Column( "runner_name", String( 255 ) ),
    Column( "stdout", TEXT ),
    Column( "stderr", TEXT ),
    Column( "info", TrimmedString ( 255 ) ),
    Column( "traceback", TEXT ),
    Column( "job_id", Integer, ForeignKey( "job.id" ), index=True, nullable=False ),
    Column( "working_directory", String(1024)),
    Column( "task_runner_name", String( 255 ) ),
    Column( "task_runner_external_id", String( 255 ) ),
    Column( "prepare_input_files_cmd", TEXT ) )

PostJobAction.table = Table("post_job_action", metadata,
    Column("id", Integer, primary_key=True),
    Column("workflow_step_id", Integer, ForeignKey( "workflow_step.id" ), index=True, nullable=False),
    Column("action_type", String(255), nullable=False),
    Column("output_name", String(255), nullable=True),
    Column("action_arguments", JSONType, nullable=True))

PostJobActionAssociation.table = Table("post_job_action_association", metadata,
    Column("id", Integer, primary_key=True),
    Column("job_id", Integer, ForeignKey( "job.id" ), index=True, nullable=False),
    Column("post_job_action_id", Integer, ForeignKey( "post_job_action.id" ), index=True, nullable=False))

DeferredJob.table = Table( "deferred_job", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "state", String( 64 ), index=True ),
    Column( "plugin", String( 128 ), index=True ),
    Column( "params", JSONType ) )

TransferJob.table = Table( "transfer_job", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "state", String( 64 ), index=True ),
    Column( "path", String( 1024 ) ),
    Column( "info", TEXT ),
    Column( "pid", Integer ),
    Column( "socket", Integer ),
    Column( "params", JSONType ) )

Event.table = Table( "event", metadata, 
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True, nullable=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True, nullable=True ),
    Column( "message", TrimmedString( 1024 ) ),
    Column( "session_id", Integer, ForeignKey( "galaxy_session.id" ), index=True, nullable=True ),
    Column( "tool_id", String( 255 ) ) )

GalaxySession.table = Table( "galaxy_session", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True, nullable=True ),
    Column( "remote_host", String( 255 ) ),
    Column( "remote_addr", String( 255 ) ),
    Column( "referer", TEXT ),
    Column( "current_history_id", Integer, ForeignKey( "history.id" ), nullable=True ),
    Column( "session_key", TrimmedString( 255 ), index=True, unique=True ), # unique 128 bit random number coerced to a string
    Column( "is_valid", Boolean, default=False ),
    Column( "prev_session_id", Integer ), # saves a reference to the previous session so we have a way to chain them together
    Column( "disk_usage", Numeric( 15, 0 ), index=True ) )

GalaxySessionToHistoryAssociation.table = Table( "galaxy_session_to_history", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "session_id", Integer, ForeignKey( "galaxy_session.id" ), index=True ),
    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True ) )

StoredWorkflow.table = Table( "stored_workflow", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True, nullable=False ),
    Column( "latest_workflow_id", Integer,
            ForeignKey( "workflow.id", use_alter=True, name='stored_workflow_latest_workflow_id_fk' ), index=True ),
    Column( "name", TEXT ),
    Column( "deleted", Boolean, default=False ),
    Column( "importable", Boolean, default=False ),
    Column( "slug", TEXT, index=True ),
    Column( "published", Boolean, index=True, default=False )
    )

Workflow.table = Table( "workflow", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "stored_workflow_id", Integer, ForeignKey( "stored_workflow.id" ), index=True, nullable=False ),
    Column( "name", TEXT ),
    Column( "has_cycles", Boolean ),
    Column( "has_errors", Boolean )
    )

WorkflowStep.table = Table( "workflow_step", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "workflow_id", Integer, ForeignKey( "workflow.id" ), index=True, nullable=False ),
    Column( "type", String(64) ),
    Column( "tool_id", TEXT ),
    Column( "tool_version", TEXT ), # Reserved for future
    Column( "tool_inputs", JSONType ),
    Column( "tool_errors", JSONType ),
    Column( "position", JSONType ),
    Column( "config", JSONType ),
    Column( "order_index", Integer ),
    ## Column( "input_connections", JSONType )
    )

WorkflowStepConnection.table = Table( "workflow_step_connection", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "output_step_id", Integer, ForeignKey( "workflow_step.id" ), index=True ),
    Column( "input_step_id", Integer, ForeignKey( "workflow_step.id" ), index=True ),
    Column( "output_name", TEXT ),
    Column( "input_name", TEXT)
    )

WorkflowOutput.table = Table( "workflow_output", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "workflow_step_id", Integer, ForeignKey("workflow_step.id"), index=True, nullable=False),
    Column( "output_name", String(255), nullable=True)
    )

WorkflowInvocation.table = Table( "workflow_invocation", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "workflow_id", Integer, ForeignKey( "workflow.id" ), index=True, nullable=False )
    )

WorkflowInvocationStep.table = Table( "workflow_invocation_step", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "workflow_invocation_id", Integer, ForeignKey( "workflow_invocation.id" ), index=True, nullable=False ),
    Column( "workflow_step_id",  Integer, ForeignKey( "workflow_step.id" ), index=True, nullable=False ),
    Column( "job_id",  Integer, ForeignKey( "job.id" ), index=True, nullable=True )
    )

StoredWorkflowUserShareAssociation.table = Table( "stored_workflow_user_share_connection", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "stored_workflow_id", Integer, ForeignKey( "stored_workflow.id" ), index=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True )
    )

StoredWorkflowMenuEntry.table = Table( "stored_workflow_menu_entry", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "stored_workflow_id", Integer, ForeignKey( "stored_workflow.id" ), index=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),                              
    Column( "order_index", Integer ) )

MetadataFile.table = Table( "metadata_file", metadata, 
    Column( "id", Integer, primary_key=True ),
    Column( "name", TEXT ),
    Column( "hda_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True, nullable=True ),
    Column( "lda_id", Integer, ForeignKey( "library_dataset_dataset_association.id" ), index=True, nullable=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, index=True, default=now, onupdate=now ),
    Column( "object_store_id", TrimmedString( 255 ), index=True ),
    Column( "deleted", Boolean, index=True, default=False ),
    Column( "purged", Boolean, index=True, default=False ) )

FormDefinitionCurrent.table = Table('form_definition_current', metadata,
    Column( "id", Integer, primary_key=True),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "latest_form_id", Integer, ForeignKey( "form_definition.id" ), index=True ),
    Column( "deleted", Boolean, index=True, default=False ))

FormDefinition.table = Table('form_definition', metadata,
    Column( "id", Integer, primary_key=True),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "name", TrimmedString( 255 ), nullable=False ),
    Column( "desc", TEXT ),
    Column( "form_definition_current_id",
            Integer, 
            ForeignKey( "form_definition_current.id", name='for_def_form_def_current_id_fk', use_alter=True ), 
            index=True ),
    Column( "fields", JSONType() ),
    Column( "type", TrimmedString( 255 ), index=True ),
    Column( "layout", JSONType() ), )

ExternalService.table = Table( 'external_service', metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "name", TrimmedString( 255 ), nullable=False ),
    Column( "description", TEXT ),
    Column( "external_service_type_id", TrimmedString( 255 ), nullable=False ),
    Column( "version", TrimmedString( 255 ) ),
    Column( "form_definition_id", Integer, ForeignKey( "form_definition.id" ), index=True ),
    Column( "form_values_id", Integer, ForeignKey( "form_values.id" ), index=True ),
    Column( "deleted", Boolean, index=True, default=False ) )

RequestType.table = Table('request_type', metadata,
    Column( "id", Integer, primary_key=True),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "name", TrimmedString( 255 ), nullable=False ),
    Column( "desc", TEXT ),
    Column( "request_form_id", Integer, ForeignKey( "form_definition.id" ), index=True ),
    Column( "sample_form_id", Integer, ForeignKey( "form_definition.id" ), index=True ),
    Column( "deleted", Boolean, index=True, default=False ) )

RequestTypeExternalServiceAssociation.table = Table( "request_type_external_service_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "request_type_id", Integer, ForeignKey( "request_type.id" ), index=True ),
    Column( "external_service_id", Integer, ForeignKey( "external_service.id" ), index=True ) )

RequestTypePermissions.table = Table( "request_type_permissions", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "action", TEXT ),
    Column( "request_type_id", Integer, ForeignKey( "request_type.id" ), nullable=True, index=True ),
    Column( "role_id", Integer, ForeignKey( "role.id" ), index=True ) )

FormValues.table = Table('form_values', metadata,
    Column( "id", Integer, primary_key=True),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "form_definition_id", Integer, ForeignKey( "form_definition.id" ), index=True ),
    Column( "content", JSONType()) )

Request.table = Table('request', metadata,
    Column( "id", Integer, primary_key=True),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "name", TrimmedString( 255 ), nullable=False ),
    Column( "desc", TEXT ),
    Column( "notification", JSONType() ),
    Column( "form_values_id", Integer, ForeignKey( "form_values.id" ), index=True ),
    Column( "request_type_id", Integer, ForeignKey( "request_type.id" ), index=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
    Column( "deleted", Boolean, index=True, default=False ) )

RequestEvent.table = Table('request_event', metadata,
    Column( "id", Integer, primary_key=True),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "request_id", Integer, ForeignKey( "request.id" ), index=True ), 
    Column( "state", TrimmedString( 255 ),  index=True ),
    Column( "comment", TEXT ) )

Sample.table = Table('sample', metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "name", TrimmedString( 255 ), nullable=False ),
    Column( "desc", TEXT ),
    Column( "form_values_id", Integer, ForeignKey( "form_values.id" ), index=True ),
    Column( "request_id", Integer, ForeignKey( "request.id" ), index=True ),
    Column( "bar_code", TrimmedString( 255 ), index=True ),
    Column( "library_id", Integer, ForeignKey( "library.id" ), index=True ),
    Column( "folder_id", Integer, ForeignKey( "library_folder.id" ), index=True ),
    Column( "deleted", Boolean, index=True, default=False ),
    Column( "workflow", JSONType, nullable=True ),
    Column( "history_id", Integer, ForeignKey( "history.id" ), nullable=True) )

SampleState.table = Table('sample_state', metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "name", TrimmedString( 255 ), nullable=False ),
    Column( "desc", TEXT ),
    Column( "request_type_id", Integer, ForeignKey( "request_type.id" ), index=True ) )

SampleEvent.table = Table('sample_event', metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "sample_id", Integer, ForeignKey( "sample.id" ), index=True ), 
    Column( "sample_state_id", Integer, ForeignKey( "sample_state.id" ), index=True ), 
    Column( "comment", TEXT ) )

SampleDataset.table = Table('sample_dataset', metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "sample_id", Integer, ForeignKey( "sample.id" ), index=True ), 
    Column( "name", TrimmedString( 255 ), nullable=False ),
    Column( "file_path", TEXT ),
    Column( "status", TrimmedString( 255 ), nullable=False ),
    Column( "error_msg", TEXT ),
    Column( "size", TrimmedString( 255 ) ),
    Column( "external_service_id", Integer, ForeignKey( "external_service.id" ), index=True ) ) 

Run.table = Table( 'run', metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "form_definition_id", Integer, ForeignKey( "form_definition.id" ), index=True ),
    Column( "form_values_id", Integer, ForeignKey( "form_values.id" ), index=True ),
    Column( "deleted", Boolean, index=True, default=False ),
    Column( "subindex", TrimmedString( 255 ), index=True ) )

RequestTypeRunAssociation.table = Table( "request_type_run_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "request_type_id", Integer, ForeignKey( "request_type.id" ), index=True, nullable=False ),
    Column( "run_id", Integer, ForeignKey( "run.id" ), index=True, nullable=False ) )

SampleRunAssociation.table = Table( "sample_run_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "sample_id", Integer, ForeignKey( "sample.id" ), index=True, nullable=False ),
    Column( "run_id", Integer, ForeignKey( "run.id" ), index=True, nullable=False ) )

Page.table = Table( "page", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True, nullable=False ),
    Column( "latest_revision_id", Integer,
            ForeignKey( "page_revision.id", use_alter=True, name='page_latest_revision_id_fk' ), index=True ),
    Column( "title", TEXT ),
    Column( "slug", TEXT, unique=True, index=True ),
    Column( "importable", Boolean, index=True, default=False ), 
    Column( "published", Boolean, index=True, default=False ), 
    Column( "deleted", Boolean, index=True, default=False ), 
    )

PageRevision.table = Table( "page_revision", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "page_id", Integer, ForeignKey( "page.id" ), index=True, nullable=False ),
    Column( "title", TEXT ),
    Column( "content", TEXT )
    )

PageUserShareAssociation.table = Table( "page_user_share_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "page_id", Integer, ForeignKey( "page.id" ), index=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True )
    )

Visualization.table = Table( "visualization", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True, nullable=False ),
    Column( "latest_revision_id", Integer,
            ForeignKey( "visualization_revision.id", use_alter=True, name='visualization_latest_revision_id_fk' ), index=True ),
    Column( "title", TEXT ),
    Column( "type", TEXT ),
    Column( "dbkey", TEXT, index=True ),
    Column( "deleted", Boolean, default=False, index=True ),
    Column( "importable", Boolean, default=False, index=True ),
    Column( "slug", TEXT, index=True ),
    Column( "published", Boolean, default=False, index=True )
    )

VisualizationRevision.table = Table( "visualization_revision", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "update_time", DateTime, default=now, onupdate=now ),
    Column( "visualization_id", Integer, ForeignKey( "visualization.id" ), index=True, nullable=False ),
    Column( "title", TEXT ),
    Column( "dbkey", TEXT, index=True ),
    Column( "config", JSONType )
    )
    
VisualizationUserShareAssociation.table = Table( "visualization_user_share_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "visualization_id", Integer, ForeignKey( "visualization.id" ), index=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True )
    )
    
# Tagging tables.

Tag.table = Table( "tag", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "type", Integer ),
    Column( "parent_id", Integer, ForeignKey( "tag.id" ) ),
    Column( "name", TrimmedString(255) ), 
    UniqueConstraint( "name" ) )

HistoryTagAssociation.table = Table( "history_tag_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True ),
    Column( "tag_id", Integer, ForeignKey( "tag.id" ), index=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
    Column( "user_tname", TrimmedString(255), index=True),
    Column( "value", TrimmedString(255), index=True),
    Column( "user_value", TrimmedString(255), index=True) )
    
DatasetTagAssociation.table = Table( "dataset_tag_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "dataset_id", Integer, ForeignKey( "dataset.id" ), index=True ),
    Column( "tag_id", Integer, ForeignKey( "tag.id" ), index=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
    Column( "user_tname", TrimmedString(255), index=True),
    Column( "value", TrimmedString(255), index=True),
    Column( "user_value", TrimmedString(255), index=True) )

HistoryDatasetAssociationTagAssociation.table = Table( "history_dataset_association_tag_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "history_dataset_association_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True ),
    Column( "tag_id", Integer, ForeignKey( "tag.id" ), index=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
    Column( "user_tname", TrimmedString(255), index=True),
    Column( "value", TrimmedString(255), index=True),
    Column( "user_value", TrimmedString(255), index=True) )
        
StoredWorkflowTagAssociation.table = Table( "stored_workflow_tag_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "stored_workflow_id", Integer, ForeignKey( "stored_workflow.id" ), index=True ),
    Column( "tag_id", Integer, ForeignKey( "tag.id" ), index=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
    Column( "user_tname", Unicode(255), index=True),
    Column( "value", Unicode(255), index=True),
    Column( "user_value", Unicode(255), index=True) )

PageTagAssociation.table = Table( "page_tag_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "page_id", Integer, ForeignKey( "page.id" ), index=True ),
    Column( "tag_id", Integer, ForeignKey( "tag.id" ), index=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
    Column( "user_tname", TrimmedString(255), index=True),
    Column( "value", TrimmedString(255), index=True),
    Column( "user_value", TrimmedString(255), index=True) )
    
WorkflowStepTagAssociation.table = Table( "workflow_step_tag_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "workflow_step_id", Integer, ForeignKey( "workflow_step.id" ), index=True ),
    Column( "tag_id", Integer, ForeignKey( "tag.id" ), index=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
    Column( "user_tname", Unicode(255), index=True),
    Column( "value", Unicode(255), index=True),
    Column( "user_value", Unicode(255), index=True) )
    
VisualizationTagAssociation.table = Table( "visualization_tag_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "visualization_id", Integer, ForeignKey( "visualization.id" ), index=True ),
    Column( "tag_id", Integer, ForeignKey( "tag.id" ), index=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
    Column( "user_tname", TrimmedString(255), index=True),
    Column( "value", TrimmedString(255), index=True),
    Column( "user_value", TrimmedString(255), index=True) )
    
ToolTagAssociation.table = Table( "tool_tag_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "tool_id", TrimmedString(255), index=True ),
    Column( "tag_id", Integer, ForeignKey( "tag.id" ), index=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
    Column( "user_tname", TrimmedString(255), index=True),
    Column( "value", TrimmedString(255), index=True),
    Column( "user_value", TrimmedString(255), index=True) )
 
# Annotation tables.

HistoryAnnotationAssociation.table = Table( "history_annotation_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
    Column( "annotation", TEXT, index=True) )

HistoryDatasetAssociationAnnotationAssociation.table = Table( "history_dataset_association_annotation_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "history_dataset_association_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
    Column( "annotation", TEXT, index=True) )

StoredWorkflowAnnotationAssociation.table = Table( "stored_workflow_annotation_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "stored_workflow_id", Integer, ForeignKey( "stored_workflow.id" ), index=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
    Column( "annotation", TEXT, index=True) )

WorkflowStepAnnotationAssociation.table = Table( "workflow_step_annotation_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "workflow_step_id", Integer, ForeignKey( "workflow_step.id" ), index=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
    Column( "annotation", TEXT, index=True) )
    
PageAnnotationAssociation.table = Table( "page_annotation_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "page_id", Integer, ForeignKey( "page.id" ), index=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
    Column( "annotation", TEXT, index=True) )
    
VisualizationAnnotationAssociation.table = Table( "visualization_annotation_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "visualization_id", Integer, ForeignKey( "visualization.id" ), index=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
    Column( "annotation", TEXT, index=True) )
    
# Ratings tables.
HistoryRatingAssociation.table = Table( "history_rating_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "history_id", Integer, ForeignKey( "history.id" ), index=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
    Column( "rating", Integer, index=True) )
    
HistoryDatasetAssociationRatingAssociation.table = Table( "history_dataset_association_rating_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "history_dataset_association_id", Integer, ForeignKey( "history_dataset_association.id" ), index=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
    Column( "rating", Integer, index=True) )
    
StoredWorkflowRatingAssociation.table = Table( "stored_workflow_rating_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "stored_workflow_id", Integer, ForeignKey( "stored_workflow.id" ), index=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
    Column( "rating", Integer, index=True) )
    
PageRatingAssociation.table = Table( "page_rating_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "page_id", Integer, ForeignKey( "page.id" ), index=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
    Column( "rating", Integer, index=True) )
    
VisualizationRatingAssociation.table = Table( "visualization_rating_association", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "visualization_id", Integer, ForeignKey( "visualization.id" ), index=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
    Column( "rating", Integer, index=True) )
    
# User tables.
    
UserPreference.table = Table( "user_preference", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
    Column( "name", Unicode( 255 ), index=True),
    Column( "value", Unicode( 1024 ) ) )
    
UserAction.table = Table( "user_action", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
    Column( "session_id", Integer, ForeignKey( "galaxy_session.id" ), index=True ),
    Column( "action", Unicode( 255 ) ),
    Column( "context", Unicode( 512 ) ),
    Column( "params", Unicode( 1024 ) ) )

APIKeys.table = Table( "api_keys", metadata,
    Column( "id", Integer, primary_key=True ),
    Column( "create_time", DateTime, default=now ),
    Column( "user_id", Integer, ForeignKey( "galaxy_user.id" ), index=True ),
    Column( "key", TrimmedString( 32 ), index=True, unique=True ) )

# With the tables defined we can define the mappers and setup the 
# relationships between the model objects.

assign_mapper( context, Sample, Sample.table,
               properties=dict( 
                    events=relation( SampleEvent, backref="sample",
                        order_by=desc( SampleEvent.table.c.update_time ) ),
                    datasets=relation( SampleDataset, backref="sample",
                        order_by=desc( SampleDataset.table.c.update_time ) ),
                    values=relation( FormValues,
                        primaryjoin=( Sample.table.c.form_values_id == FormValues.table.c.id ) ),
                    request=relation( Request,
                        primaryjoin=( Sample.table.c.request_id == Request.table.c.id ) ),
                    folder=relation( LibraryFolder,
                        primaryjoin=( Sample.table.c.folder_id == LibraryFolder.table.c.id ) ),                 
                    library=relation( Library,
                        primaryjoin=( Sample.table.c.library_id == Library.table.c.id ) ),
                    history=relation( History,
                        primaryjoin=( Sample.table.c.history_id == History.table.c.id ) ),
            ) )

assign_mapper( context, FormValues, FormValues.table,
               properties=dict( form_definition=relation( FormDefinition,
                                                          primaryjoin=( FormValues.table.c.form_definition_id == FormDefinition.table.c.id ) )
             )
)

assign_mapper( context, Request, Request.table,
               properties=dict( values=relation( FormValues,
                                                 primaryjoin=( Request.table.c.form_values_id == FormValues.table.c.id ) ),
                                type=relation( RequestType,
                                               primaryjoin=( Request.table.c.request_type_id == RequestType.table.c.id ) ),
                                user=relation( User,
                                               primaryjoin=( Request.table.c.user_id == User.table.c.id ),
                                               backref="requests" ),
                                samples=relation( Sample,
                                                  primaryjoin=( Request.table.c.id == Sample.table.c.request_id ),
                                                  order_by=asc( Sample.table.c.id ) ),
                                events=relation( RequestEvent, backref="request",
                                                 order_by=desc( RequestEvent.table.c.update_time ) )
                              ) )

assign_mapper( context, RequestEvent, RequestEvent.table,
               properties=None )

assign_mapper( context, ExternalService, ExternalService.table,
               properties=dict( form_definition=relation( FormDefinition,
                                                       primaryjoin=( ExternalService.table.c.form_definition_id == FormDefinition.table.c.id ) ),
                                form_values=relation( FormValues,
                                                      primaryjoin=( ExternalService.table.c.form_values_id == FormValues.table.c.id ) ) 
                                ) )

assign_mapper( context, RequestType, RequestType.table,               
               properties=dict( states=relation( SampleState, 
                                                 backref="request_type",
                                                 primaryjoin=( RequestType.table.c.id == SampleState.table.c.request_type_id ),
                                                 order_by=asc( SampleState.table.c.update_time ) ),
                                request_form=relation( FormDefinition,
                                                       primaryjoin=( RequestType.table.c.request_form_id == FormDefinition.table.c.id ) ),
                                sample_form=relation( FormDefinition,
                                                      primaryjoin=( RequestType.table.c.sample_form_id == FormDefinition.table.c.id ) ),
                              ) )

assign_mapper( context, RequestTypeExternalServiceAssociation, RequestTypeExternalServiceAssociation.table,
    properties=dict(
        request_type=relation( RequestType, 
                               primaryjoin=( ( RequestTypeExternalServiceAssociation.table.c.request_type_id == RequestType.table.c.id ) ), backref="external_service_associations" ),
        external_service=relation( ExternalService,
                                   primaryjoin=( RequestTypeExternalServiceAssociation.table.c.external_service_id == ExternalService.table.c.id ) )
    )
)


assign_mapper( context, RequestTypePermissions, RequestTypePermissions.table,
    properties=dict(
        request_type=relation( RequestType, backref="actions" ),
        role=relation( Role, backref="request_type_actions" )
    )
)

assign_mapper( context, FormDefinition, FormDefinition.table,
               properties=dict( current=relation( FormDefinitionCurrent,
                                                  primaryjoin=( FormDefinition.table.c.form_definition_current_id == FormDefinitionCurrent.table.c.id ) )
                              ) )
assign_mapper( context, FormDefinitionCurrent, FormDefinitionCurrent.table,
                properties=dict( forms=relation( FormDefinition, backref='form_definition_current',
                                                 cascade="all, delete-orphan",
                                                 primaryjoin=( FormDefinitionCurrent.table.c.id == FormDefinition.table.c.form_definition_current_id ) ),
                                 latest_form=relation( FormDefinition, post_update=True,
                                                       primaryjoin=( FormDefinitionCurrent.table.c.latest_form_id == FormDefinition.table.c.id ) )
                               ) )

assign_mapper( context, SampleEvent, SampleEvent.table, 
               properties=dict( state=relation( SampleState,
                                                primaryjoin=( SampleEvent.table.c.sample_state_id == SampleState.table.c.id ) ),

                                ) )
                                
assign_mapper( context, SampleState, SampleState.table,
               properties=None )

assign_mapper( context, SampleDataset, SampleDataset.table,
               properties=dict( external_service=relation( ExternalService,
                                                           primaryjoin=( SampleDataset.table.c.external_service_id == ExternalService.table.c.id ) )
    ) 
)


assign_mapper( context, SampleRunAssociation, SampleRunAssociation.table,
               properties=dict( sample=relation( Sample, backref="runs", order_by=desc( Run.table.c.update_time ) ),
                                run=relation( Run, backref="samples", order_by=asc( Sample.table.c.id ) ) ) )

assign_mapper( context, RequestTypeRunAssociation, RequestTypeRunAssociation.table,
               properties=dict( request_type=relation( RequestType, backref="run" ),
                                run=relation( Run, backref="request_type" ) ) )

assign_mapper( context, Run, Run.table,
                properties=dict( template=relation( FormDefinition,
                                                    primaryjoin=( Run.table.c.form_definition_id == FormDefinition.table.c.id ) ), 
                                 info=relation( FormValues,
                                                primaryjoin=( Run.table.c.form_values_id == FormValues.table.c.id ) ) ) )

assign_mapper( context, UserAddress, UserAddress.table,
               properties=dict( 
                    user=relation( User,
                                   primaryjoin=( UserAddress.table.c.user_id == User.table.c.id ),
                                   backref='addresses',
                                   order_by=desc(UserAddress.table.c.update_time)), 
                ) )

assign_mapper( context, UserOpenID, UserOpenID.table,
    properties=dict( 
        session=relation( GalaxySession,
                          primaryjoin=( UserOpenID.table.c.session_id == GalaxySession.table.c.id ),
                          backref='openids',
                          order_by=desc( UserOpenID.table.c.update_time ) ),
        user=relation( User,
                       primaryjoin=( UserOpenID.table.c.user_id == User.table.c.id ),
                       backref='openids',
                       order_by=desc( UserOpenID.table.c.update_time ) ) ) )


assign_mapper( context, ValidationError, ValidationError.table )

assign_mapper( context, HistoryDatasetAssociation, HistoryDatasetAssociation.table,
    properties=dict( 
        dataset=relation( 
            Dataset, 
            primaryjoin=( Dataset.table.c.id == HistoryDatasetAssociation.table.c.dataset_id ), lazy=False ),
        # .history defined in History mapper
        copied_to_history_dataset_associations=relation( 
            HistoryDatasetAssociation, 
            primaryjoin=( HistoryDatasetAssociation.table.c.copied_from_history_dataset_association_id == HistoryDatasetAssociation.table.c.id ),
            backref=backref( "copied_from_history_dataset_association", primaryjoin=( HistoryDatasetAssociation.table.c.copied_from_history_dataset_association_id == HistoryDatasetAssociation.table.c.id ), remote_side=[HistoryDatasetAssociation.table.c.id], uselist=False ) ),
        copied_to_library_dataset_dataset_associations=relation( 
            LibraryDatasetDatasetAssociation, 
            primaryjoin=( HistoryDatasetAssociation.table.c.copied_from_library_dataset_dataset_association_id == LibraryDatasetDatasetAssociation.table.c.id ),
            backref=backref( "copied_from_history_dataset_association", primaryjoin=( HistoryDatasetAssociation.table.c.copied_from_library_dataset_dataset_association_id == LibraryDatasetDatasetAssociation.table.c.id ), remote_side=[LibraryDatasetDatasetAssociation.table.c.id], uselist=False ) ),
        implicitly_converted_datasets=relation( 
            ImplicitlyConvertedDatasetAssociation, 
            primaryjoin=( ImplicitlyConvertedDatasetAssociation.table.c.hda_parent_id == HistoryDatasetAssociation.table.c.id ) ),
        implicitly_converted_parent_datasets=relation( 
            ImplicitlyConvertedDatasetAssociation, 
            primaryjoin=( ImplicitlyConvertedDatasetAssociation.table.c.hda_id == HistoryDatasetAssociation.table.c.id ) ),
        children=relation( 
            HistoryDatasetAssociation, 
            primaryjoin=( HistoryDatasetAssociation.table.c.parent_id == HistoryDatasetAssociation.table.c.id ),
            backref=backref( "parent", primaryjoin=( HistoryDatasetAssociation.table.c.parent_id == HistoryDatasetAssociation.table.c.id ), remote_side=[HistoryDatasetAssociation.table.c.id], uselist=False ) ),
        visible_children=relation( 
            HistoryDatasetAssociation, 
            primaryjoin=( ( HistoryDatasetAssociation.table.c.parent_id == HistoryDatasetAssociation.table.c.id ) & ( HistoryDatasetAssociation.table.c.visible == True ) ) ),
        tags=relation( HistoryDatasetAssociationTagAssociation, order_by=HistoryDatasetAssociationTagAssociation.table.c.id, backref='history_tag_associations' ),
        annotations=relation( HistoryDatasetAssociationAnnotationAssociation, order_by=HistoryDatasetAssociationAnnotationAssociation.table.c.id, backref="hdas" ),
        ratings=relation( HistoryDatasetAssociationRatingAssociation, order_by=HistoryDatasetAssociationRatingAssociation.table.c.id, backref="hdas" ) )
            )

assign_mapper( context, Dataset, Dataset.table,
    properties=dict( 
        history_associations=relation( 
            HistoryDatasetAssociation, 
            primaryjoin=( Dataset.table.c.id == HistoryDatasetAssociation.table.c.dataset_id ) ),
        active_history_associations=relation( 
            HistoryDatasetAssociation, 
            primaryjoin=( ( Dataset.table.c.id == HistoryDatasetAssociation.table.c.dataset_id ) & ( HistoryDatasetAssociation.table.c.deleted == False ) & ( HistoryDatasetAssociation.table.c.purged == False ) ) ),
        purged_history_associations=relation(
            HistoryDatasetAssociation,
            primaryjoin=( ( Dataset.table.c.id == HistoryDatasetAssociation.table.c.dataset_id ) & ( HistoryDatasetAssociation.table.c.purged == True ) ) ),
        library_associations=relation( 
            LibraryDatasetDatasetAssociation, 
            primaryjoin=( Dataset.table.c.id == LibraryDatasetDatasetAssociation.table.c.dataset_id ) ),
        active_library_associations=relation( 
            LibraryDatasetDatasetAssociation, 
            primaryjoin=( ( Dataset.table.c.id == LibraryDatasetDatasetAssociation.table.c.dataset_id ) & ( LibraryDatasetDatasetAssociation.table.c.deleted == False ) ) ),
        tags=relation(DatasetTagAssociation, order_by=DatasetTagAssociation.table.c.id, backref='datasets')
            ) )

assign_mapper( context, HistoryDatasetAssociationDisplayAtAuthorization, HistoryDatasetAssociationDisplayAtAuthorization.table,
    properties=dict( history_dataset_association = relation( HistoryDatasetAssociation ),
                     user = relation( User ) ) )
                     
assign_mapper( context, HistoryDatasetAssociationSubset, HistoryDatasetAssociationSubset.table,
    properties=dict( hda = relation( HistoryDatasetAssociation,
                        primaryjoin=( HistoryDatasetAssociationSubset.table.c.history_dataset_association_id == HistoryDatasetAssociation.table.c.id ) ),
                     subset = relation( HistoryDatasetAssociation,
                        primaryjoin=( HistoryDatasetAssociationSubset.table.c.history_dataset_association_subset_id == HistoryDatasetAssociation.table.c.id ) )
                    ) )

assign_mapper( context, ImplicitlyConvertedDatasetAssociation, ImplicitlyConvertedDatasetAssociation.table, 
    properties=dict( parent_hda=relation( 
                        HistoryDatasetAssociation, 
                        primaryjoin=( ImplicitlyConvertedDatasetAssociation.table.c.hda_parent_id == HistoryDatasetAssociation.table.c.id ) ),
                     parent_ldda=relation( 
                        LibraryDatasetDatasetAssociation, 
                        primaryjoin=( ImplicitlyConvertedDatasetAssociation.table.c.ldda_parent_id == LibraryDatasetDatasetAssociation.table.c.id ) ),
                     dataset_ldda=relation( 
                        LibraryDatasetDatasetAssociation, 
                        primaryjoin=( ImplicitlyConvertedDatasetAssociation.table.c.ldda_id == LibraryDatasetDatasetAssociation.table.c.id ) ),
                     dataset=relation( 
                        HistoryDatasetAssociation, 
                        primaryjoin=( ImplicitlyConvertedDatasetAssociation.table.c.hda_id == HistoryDatasetAssociation.table.c.id ) ) ) )

assign_mapper( context, History, History.table,
    properties=dict( galaxy_sessions=relation( GalaxySessionToHistoryAssociation ),
                     datasets=relation( HistoryDatasetAssociation, backref="history", order_by=asc(HistoryDatasetAssociation.table.c.hid) ),
                     active_datasets=relation( HistoryDatasetAssociation, primaryjoin=( ( HistoryDatasetAssociation.table.c.history_id == History.table.c.id ) & not_( HistoryDatasetAssociation.table.c.deleted ) ), order_by=asc( HistoryDatasetAssociation.table.c.hid ), viewonly=True ),
                     visible_datasets=relation( HistoryDatasetAssociation, primaryjoin=( ( HistoryDatasetAssociation.table.c.history_id == History.table.c.id ) & not_( HistoryDatasetAssociation.table.c.deleted ) & HistoryDatasetAssociation.table.c.visible ),
                     order_by=asc( HistoryDatasetAssociation.table.c.hid ), viewonly=True ),
                     tags=relation( HistoryTagAssociation, order_by=HistoryTagAssociation.table.c.id, backref="histories" ),
                     annotations=relation( HistoryAnnotationAssociation, order_by=HistoryAnnotationAssociation.table.c.id, backref="histories" ),
                     ratings=relation( HistoryRatingAssociation, order_by=HistoryRatingAssociation.table.c.id, backref="histories" ) )  
                      )

# Set up proxy so that 
#   History.users_shared_with
# returns a list of users that history is shared with.
History.users_shared_with_dot_users = association_proxy( 'users_shared_with', 'user' )

assign_mapper( context, HistoryUserShareAssociation, HistoryUserShareAssociation.table,
    properties=dict( user=relation( User, backref='histories_shared_by_others' ),
                     history=relation( History, backref='users_shared_with' )
                   ) )

assign_mapper( context, User, User.table, 
    properties=dict( histories=relation( History, backref="user",
                                         order_by=desc(History.table.c.update_time) ),               
                     active_histories=relation( History, primaryjoin=( ( History.table.c.user_id == User.table.c.id ) & ( not_( History.table.c.deleted ) ) ), order_by=desc( History.table.c.update_time ) ),
                     galaxy_sessions=relation( GalaxySession, order_by=desc( GalaxySession.table.c.update_time ) ),
                     stored_workflow_menu_entries=relation( StoredWorkflowMenuEntry, backref="user",
                                                            cascade="all, delete-orphan",
                                                            collection_class=ordering_list( 'order_index' ) ),
                     _preferences=relation( UserPreference, backref="user", collection_class=attribute_mapped_collection('name')),
#                     addresses=relation( UserAddress,
#                                         primaryjoin=( User.table.c.id == UserAddress.table.c.user_id ) )
                     values=relation( FormValues,
                                      primaryjoin=( User.table.c.form_values_id == FormValues.table.c.id ) ),
                     api_keys=relation( APIKeys, backref="user", order_by=desc( APIKeys.table.c.create_time ) ),
                     ) )
                     
# Set up proxy so that this syntax is possible:
# <user_obj>.preferences[pref_name] = pref_value
User.preferences = association_proxy('_preferences', 'value', creator=UserPreference)

assign_mapper( context, Group, Group.table,
    properties=dict( users=relation( UserGroupAssociation ) ) )

assign_mapper( context, UserGroupAssociation, UserGroupAssociation.table,
    properties=dict( user=relation( User, backref = "groups" ),
                     group=relation( Group, backref = "members" ) ) )

assign_mapper( context, DefaultUserPermissions, DefaultUserPermissions.table,
    properties=dict( user=relation( User, backref = "default_permissions" ),
                     role=relation( Role ) ) )

assign_mapper( context, DefaultHistoryPermissions, DefaultHistoryPermissions.table,
    properties=dict( history=relation( History, backref = "default_permissions" ),
                     role=relation( Role ) ) )

assign_mapper( context, Role, Role.table,
    properties=dict(
        users=relation( UserRoleAssociation ),
        groups=relation( GroupRoleAssociation )
    )
)

assign_mapper( context, UserRoleAssociation, UserRoleAssociation.table,
    properties=dict(
        user=relation( User, backref="roles" ),
        non_private_roles=relation( User, 
                                    backref="non_private_roles",
                                    primaryjoin=( ( User.table.c.id == UserRoleAssociation.table.c.user_id ) & ( UserRoleAssociation.table.c.role_id == Role.table.c.id ) & not_( Role.table.c.name == User.table.c.email ) ) ),
        role=relation( Role )
    )
)

assign_mapper( context, GroupRoleAssociation, GroupRoleAssociation.table,
    properties=dict(
        group=relation( Group, backref="roles" ),
        role=relation( Role )
    )
)

assign_mapper( context, Quota, Quota.table,
    properties=dict( users=relation( UserQuotaAssociation ),
                     groups=relation( GroupQuotaAssociation ) ) )

assign_mapper( context, UserQuotaAssociation, UserQuotaAssociation.table,
    properties=dict( user=relation( User, backref="quotas" ),
                     quota=relation( Quota ) ) )

assign_mapper( context, GroupQuotaAssociation, GroupQuotaAssociation.table,
    properties=dict( group=relation( Group, backref="quotas" ),
                     quota=relation( Quota ) ) )

assign_mapper( context, DefaultQuotaAssociation, DefaultQuotaAssociation.table,
    properties=dict( quota=relation( Quota, backref="default" ) ) )

assign_mapper( context, DatasetPermissions, DatasetPermissions.table,
    properties=dict(
        dataset=relation( Dataset, backref="actions" ),
        role=relation( Role, backref="dataset_actions" )
    )
)

assign_mapper( context, LibraryPermissions, LibraryPermissions.table,
    properties=dict(
        library=relation( Library, backref="actions" ),
        role=relation( Role, backref="library_actions" )
    )
)

assign_mapper( context, LibraryFolderPermissions, LibraryFolderPermissions.table,
    properties=dict(
        folder=relation( LibraryFolder, backref="actions" ),
        role=relation( Role, backref="library_folder_actions" )
    )
)

assign_mapper( context, LibraryDatasetPermissions, LibraryDatasetPermissions.table,
    properties=dict(
        library_dataset=relation( LibraryDataset, backref="actions" ),
        role=relation( Role, backref="library_dataset_actions" )
    )
)

assign_mapper( context, LibraryDatasetDatasetAssociationPermissions, LibraryDatasetDatasetAssociationPermissions.table,
    properties=dict(
        library_dataset_dataset_association = relation( LibraryDatasetDatasetAssociation, backref="actions" ),
        role=relation( Role, backref="library_dataset_dataset_actions" )
    )
)

assign_mapper( context, Library, Library.table,
    properties=dict(
        root_folder=relation( LibraryFolder, backref=backref( "library_root" ) )
    )
)

assign_mapper( context, LibraryInfoAssociation, LibraryInfoAssociation.table,
               properties=dict( library=relation( Library,
                                                  primaryjoin=( ( LibraryInfoAssociation.table.c.library_id == Library.table.c.id ) & ( not_( LibraryInfoAssociation.table.c.deleted ) ) ), backref="info_association" ),
                                template=relation( FormDefinition,
                                                   primaryjoin=( LibraryInfoAssociation.table.c.form_definition_id == FormDefinition.table.c.id ) ), 
                                info=relation( FormValues,
                                               primaryjoin=( LibraryInfoAssociation.table.c.form_values_id == FormValues.table.c.id ) )
                              ) )

assign_mapper( context, LibraryFolder, LibraryFolder.table,
    properties=dict(
        folders=relation(
            LibraryFolder,
            primaryjoin=( LibraryFolder.table.c.parent_id == LibraryFolder.table.c.id ),
            order_by=asc( LibraryFolder.table.c.name ),
            backref=backref( "parent", primaryjoin=( LibraryFolder.table.c.parent_id == LibraryFolder.table.c.id ), remote_side=[LibraryFolder.table.c.id] ) ),
        active_folders=relation( LibraryFolder,
            primaryjoin=( ( LibraryFolder.table.c.parent_id == LibraryFolder.table.c.id ) & ( not_( LibraryFolder.table.c.deleted ) ) ),
            order_by=asc( LibraryFolder.table.c.name ),
            lazy=True, #"""sqlalchemy.exceptions.ArgumentError: Error creating eager relationship 'active_folders' on parent class '<class 'galaxy.model.LibraryFolder'>' to child class '<class 'galaxy.model.LibraryFolder'>': Cant use eager loading on a self referential relationship."""
            viewonly=True ),
        datasets=relation( LibraryDataset,
            primaryjoin=( ( LibraryDataset.table.c.folder_id == LibraryFolder.table.c.id ) ),
            order_by=asc( LibraryDataset.table.c._name ),
            lazy=True,
            viewonly=True ),
        active_datasets=relation( LibraryDataset,
            primaryjoin=( ( LibraryDataset.table.c.folder_id == LibraryFolder.table.c.id ) & ( not_( LibraryDataset.table.c.deleted ) ) ),
            order_by=asc( LibraryDataset.table.c._name ),
            lazy=True,
            viewonly=True )
    ) )

assign_mapper( context, LibraryFolderInfoAssociation, LibraryFolderInfoAssociation.table,
               properties=dict( folder=relation( LibraryFolder,
                                                 primaryjoin=( ( LibraryFolderInfoAssociation.table.c.library_folder_id == LibraryFolder.table.c.id ) & ( not_( LibraryFolderInfoAssociation.table.c.deleted ) ) ), backref="info_association" ),
                                template=relation( FormDefinition,
                                                   primaryjoin=( LibraryFolderInfoAssociation.table.c.form_definition_id == FormDefinition.table.c.id ) ), 
                                info=relation( FormValues,
                                               primaryjoin=( LibraryFolderInfoAssociation.table.c.form_values_id == FormValues.table.c.id ) )
                              ) )

assign_mapper( context, LibraryDataset, LibraryDataset.table,
    properties=dict( 
        folder=relation( LibraryFolder ),
        library_dataset_dataset_association=relation( LibraryDatasetDatasetAssociation, primaryjoin=( LibraryDataset.table.c.library_dataset_dataset_association_id == LibraryDatasetDatasetAssociation.table.c.id ) ),
        expired_datasets = relation( LibraryDatasetDatasetAssociation, foreign_keys=[LibraryDataset.table.c.id,LibraryDataset.table.c.library_dataset_dataset_association_id ], primaryjoin=( ( LibraryDataset.table.c.id == LibraryDatasetDatasetAssociation.table.c.library_dataset_id ) & ( not_( LibraryDataset.table.c.library_dataset_dataset_association_id == LibraryDatasetDatasetAssociation.table.c.id ) ) ), viewonly=True, uselist=True )
        ) )

assign_mapper( context, LibraryDatasetDatasetAssociation, LibraryDatasetDatasetAssociation.table,
    properties=dict( 
        dataset=relation( Dataset ),
        library_dataset = relation( LibraryDataset,
        primaryjoin=( LibraryDatasetDatasetAssociation.table.c.library_dataset_id == LibraryDataset.table.c.id ) ),
        user=relation( User.mapper ),
        copied_to_library_dataset_dataset_associations=relation( 
            LibraryDatasetDatasetAssociation, 
            primaryjoin=( LibraryDatasetDatasetAssociation.table.c.copied_from_library_dataset_dataset_association_id == LibraryDatasetDatasetAssociation.table.c.id ),
            backref=backref( "copied_from_library_dataset_dataset_association", primaryjoin=( LibraryDatasetDatasetAssociation.table.c.copied_from_library_dataset_dataset_association_id == LibraryDatasetDatasetAssociation.table.c.id ), remote_side=[LibraryDatasetDatasetAssociation.table.c.id] ) ),
        copied_to_history_dataset_associations=relation( 
            HistoryDatasetAssociation, 
            primaryjoin=( HistoryDatasetAssociation.table.c.copied_from_library_dataset_dataset_association_id == LibraryDatasetDatasetAssociation.table.c.id ),
            backref=backref( "copied_from_library_dataset_dataset_association", primaryjoin=( HistoryDatasetAssociation.table.c.copied_from_library_dataset_dataset_association_id == LibraryDatasetDatasetAssociation.table.c.id ), remote_side=[LibraryDatasetDatasetAssociation.table.c.id], uselist=False ) ),
        implicitly_converted_datasets=relation( 
            ImplicitlyConvertedDatasetAssociation, 
            primaryjoin=( ImplicitlyConvertedDatasetAssociation.table.c.ldda_parent_id == LibraryDatasetDatasetAssociation.table.c.id ) ),
        children=relation( 
            LibraryDatasetDatasetAssociation, 
            primaryjoin=( LibraryDatasetDatasetAssociation.table.c.parent_id == LibraryDatasetDatasetAssociation.table.c.id ),
            backref=backref( "parent", primaryjoin=( LibraryDatasetDatasetAssociation.table.c.parent_id == LibraryDatasetDatasetAssociation.table.c.id ), remote_side=[LibraryDatasetDatasetAssociation.table.c.id] ) ),
        visible_children=relation( 
            LibraryDatasetDatasetAssociation, 
            primaryjoin=( ( LibraryDatasetDatasetAssociation.table.c.parent_id == LibraryDatasetDatasetAssociation.table.c.id ) & ( LibraryDatasetDatasetAssociation.table.c.visible == True ) ) )
        ) )

assign_mapper( context, LibraryDatasetDatasetInfoAssociation, LibraryDatasetDatasetInfoAssociation.table,
               properties=dict( library_dataset_dataset_association=relation( LibraryDatasetDatasetAssociation,
                                                                              primaryjoin=( ( LibraryDatasetDatasetInfoAssociation.table.c.library_dataset_dataset_association_id == LibraryDatasetDatasetAssociation.table.c.id ) & ( not_( LibraryDatasetDatasetInfoAssociation.table.c.deleted ) ) ), backref="info_association" ),
                                template=relation( FormDefinition,
                                                   primaryjoin=( LibraryDatasetDatasetInfoAssociation.table.c.form_definition_id == FormDefinition.table.c.id ) ), 
                                info=relation( FormValues,
                                               primaryjoin=( LibraryDatasetDatasetInfoAssociation.table.c.form_values_id == FormValues.table.c.id ) )
                              ) )

assign_mapper( context, JobToInputDatasetAssociation, JobToInputDatasetAssociation.table,
    properties=dict( job=relation( Job ), dataset=relation( HistoryDatasetAssociation, lazy=False ) ) )

assign_mapper( context, JobToOutputDatasetAssociation, JobToOutputDatasetAssociation.table,
    properties=dict( job=relation( Job ), dataset=relation( HistoryDatasetAssociation, lazy=False ) ) )

assign_mapper( context, JobToInputLibraryDatasetAssociation, JobToInputLibraryDatasetAssociation.table,
    properties=dict( job=relation( Job ), dataset=relation( LibraryDatasetDatasetAssociation, lazy=False ) ) )

assign_mapper( context, JobToOutputLibraryDatasetAssociation, JobToOutputLibraryDatasetAssociation.table,
    properties=dict( job=relation( Job ), dataset=relation( LibraryDatasetDatasetAssociation, lazy=False ) ) )

assign_mapper( context, JobParameter, JobParameter.table )

assign_mapper( context, JobExternalOutputMetadata, JobExternalOutputMetadata.table,
    properties=dict( job = relation( Job ), 
                     history_dataset_association = relation( HistoryDatasetAssociation, lazy = False ),
                     library_dataset_dataset_association = relation( LibraryDatasetDatasetAssociation, lazy = False ) ) )
                     
assign_mapper( context, JobExportHistoryArchive, JobExportHistoryArchive.table,
    properties=dict( job = relation( Job ),
                     history = relation( History ),
                     dataset = relation( Dataset ) ) )
                     
assign_mapper( context, JobImportHistoryArchive, JobImportHistoryArchive.table,
    properties=dict( job = relation( Job ), history = relation( History ) ) )

assign_mapper( context, PostJobAction, PostJobAction.table,
    properties=dict(workflow_step = relation( WorkflowStep, backref='post_job_actions', primaryjoin=(WorkflowStep.table.c.id == PostJobAction.table.c.workflow_step_id))))

assign_mapper( context, PostJobActionAssociation, PostJobActionAssociation.table,
    properties=dict( job = relation( Job ), 
                     post_job_action = relation( PostJobAction) ) )

assign_mapper( context, Job, Job.table, 
    properties=dict( user=relation( User.mapper ),
                     galaxy_session=relation( GalaxySession ),
                     history=relation( History ),
                     library_folder=relation( LibraryFolder ),
                     parameters=relation( JobParameter, lazy=False ),
                     input_datasets=relation( JobToInputDatasetAssociation ),
                     output_datasets=relation( JobToOutputDatasetAssociation ),
                     post_job_actions=relation( PostJobActionAssociation, lazy=False ),
                     input_library_datasets=relation( JobToInputLibraryDatasetAssociation ),
                     output_library_datasets=relation( JobToOutputLibraryDatasetAssociation ),
                     external_output_metadata = relation( JobExternalOutputMetadata, lazy = False ) ) )

assign_mapper( context, Task, Task.table,
    properties=dict( job = relation( Job )))
                     
assign_mapper( context, DeferredJob, DeferredJob.table, 
    properties = {} )

assign_mapper( context, TransferJob, TransferJob.table, 
    properties = {} )
    
assign_mapper( context, Event, Event.table,
    properties=dict( history=relation( History ),
                     galaxy_session=relation( GalaxySession ),
                     user=relation( User.mapper ) ) )

assign_mapper( context, GalaxySession, GalaxySession.table,
    properties=dict( histories=relation( GalaxySessionToHistoryAssociation ),
                     current_history=relation( History ),
                     user=relation( User.mapper ) ) )

assign_mapper( context, GalaxySessionToHistoryAssociation, GalaxySessionToHistoryAssociation.table,
    properties=dict( galaxy_session=relation( GalaxySession ), 
                     history=relation( History ) ) )

HistoryDatasetAssociation.mapper.add_property( "creating_job_associations", relation( JobToOutputDatasetAssociation ) )
LibraryDatasetDatasetAssociation.mapper.add_property( "creating_job_associations", relation( JobToOutputLibraryDatasetAssociation ) )

assign_mapper( context, Workflow, Workflow.table,
    properties=dict( steps=relation( WorkflowStep, backref='workflow',
                                     order_by=asc(WorkflowStep.table.c.order_index),
                                     cascade="all, delete-orphan",
                                     lazy=False ),
                     # outputs = relation( WorkflowOutput, backref='workflow',
                     #                 primaryjoin=(Workflow.table.c.id == WorkflowStep.table.c.workflow_id),
                     #                 secondaryjoin=(WorkflowStep.table.c.id == WorkflowOutput.table.c.workflow_step_id))
                                      ) )

assign_mapper( context, WorkflowStep, WorkflowStep.table,
                properties=dict(
                    tags=relation(WorkflowStepTagAssociation, order_by=WorkflowStepTagAssociation.table.c.id, backref="workflow_steps"), 
                    annotations=relation( WorkflowStepAnnotationAssociation, order_by=WorkflowStepAnnotationAssociation.table.c.id, backref="workflow_steps" ) )
                )

assign_mapper( context, WorkflowOutput, WorkflowOutput.table,
    properties=dict(workflow_step = relation( WorkflowStep, backref='workflow_outputs', primaryjoin=(WorkflowStep.table.c.id == WorkflowOutput.table.c.workflow_step_id))))

assign_mapper( context, WorkflowStepConnection, WorkflowStepConnection.table,
    properties=dict( input_step=relation( WorkflowStep, backref="input_connections", cascade="all",
                                          primaryjoin=( WorkflowStepConnection.table.c.input_step_id == WorkflowStep.table.c.id ) ),
                     output_step=relation( WorkflowStep, backref="output_connections", cascade="all",
                                           primaryjoin=( WorkflowStepConnection.table.c.output_step_id == WorkflowStep.table.c.id ) ) ) )


assign_mapper( context, StoredWorkflow, StoredWorkflow.table,
    properties=dict( user=relation( User,
                                    primaryjoin=( User.table.c.id == StoredWorkflow.table.c.user_id ),
                                    backref='stored_workflows' ),
                     workflows=relation( Workflow, backref='stored_workflow',
                                         cascade="all, delete-orphan",
                                         primaryjoin=( StoredWorkflow.table.c.id == Workflow.table.c.stored_workflow_id ) ),
                     latest_workflow=relation( Workflow, post_update=True,
                                               primaryjoin=( StoredWorkflow.table.c.latest_workflow_id == Workflow.table.c.id ),
                                               lazy=False ),
                     tags=relation( StoredWorkflowTagAssociation, order_by=StoredWorkflowTagAssociation.table.c.id, backref="stored_workflows" ),
                     owner_tags=relation( StoredWorkflowTagAssociation,
                                    primaryjoin=and_( StoredWorkflow.table.c.id == StoredWorkflowTagAssociation.table.c.stored_workflow_id,
                                                      StoredWorkflow.table.c.user_id == StoredWorkflowTagAssociation.table.c.user_id ),
                                    order_by=StoredWorkflowTagAssociation.table.c.id ),
                     annotations=relation( StoredWorkflowAnnotationAssociation, order_by=StoredWorkflowAnnotationAssociation.table.c.id, backref="stored_workflows" ),
                     ratings=relation( StoredWorkflowRatingAssociation, order_by=StoredWorkflowRatingAssociation.table.c.id, backref="stored_workflows" ) )
                   )

# Set up proxy so that 
#   StoredWorkflow.users_shared_with
# returns a list of users that workflow is shared with.
StoredWorkflow.users_shared_with_dot_users = association_proxy( 'users_shared_with', 'user' )

assign_mapper( context, StoredWorkflowUserShareAssociation, StoredWorkflowUserShareAssociation.table,
    properties=dict( user=relation( User, backref='workflows_shared_by_others' ),
                     stored_workflow=relation( StoredWorkflow, backref='users_shared_with' )
                   ) )

assign_mapper( context, StoredWorkflowMenuEntry, StoredWorkflowMenuEntry.table,
    properties=dict( stored_workflow=relation( StoredWorkflow ) ) )

assign_mapper( context, WorkflowInvocation, WorkflowInvocation.table,
    properties=dict(
        steps=relation( WorkflowInvocationStep, backref='workflow_invocation', lazy=False ),
        workflow=relation( Workflow ) ) )

assign_mapper( context, WorkflowInvocationStep, WorkflowInvocationStep.table,
    properties=dict(
        workflow_step = relation( WorkflowStep ),
        job = relation( Job, backref=backref( 'workflow_invocation_step', uselist=False ) ) ) )

assign_mapper( context, MetadataFile, MetadataFile.table,
    properties=dict( history_dataset=relation( HistoryDatasetAssociation ), library_dataset=relation( LibraryDatasetDatasetAssociation ) ) )

assign_mapper( context, PageRevision, PageRevision.table )

assign_mapper( context, Page, Page.table,
    properties=dict( user=relation( User ),
                     revisions=relation( PageRevision, backref='page',
                                         cascade="all, delete-orphan",
                                         primaryjoin=( Page.table.c.id == PageRevision.table.c.page_id ) ),
                     latest_revision=relation( PageRevision, post_update=True,
                                               primaryjoin=( Page.table.c.latest_revision_id == PageRevision.table.c.id ),
                                               lazy=False ),
                     tags=relation(PageTagAssociation, order_by=PageTagAssociation.table.c.id, backref="pages"),
                     annotations=relation( PageAnnotationAssociation, order_by=PageAnnotationAssociation.table.c.id, backref="pages" ),
                     ratings=relation( PageRatingAssociation, order_by=PageRatingAssociation.table.c.id, backref="pages" )  
                   ) )
                   
assign_mapper( context, ToolShedRepository, ToolShedRepository.table,
    properties=dict( tool_versions=relation( ToolVersion,
                                             primaryjoin=( ToolShedRepository.table.c.id == ToolVersion.table.c.tool_shed_repository_id ),
                                             backref='tool_shed_repository' ) ) )

assign_mapper( context, ToolVersion, ToolVersion.table )

assign_mapper( context, ToolVersionAssociation, ToolVersionAssociation.table )

# Set up proxy so that 
#   Page.users_shared_with
# returns a list of users that page is shared with.
Page.users_shared_with_dot_users = association_proxy( 'users_shared_with', 'user' )
                   
assign_mapper( context, PageUserShareAssociation, PageUserShareAssociation.table,
   properties=dict( user=relation( User, backref='pages_shared_by_others' ),
                    page=relation( Page, backref='users_shared_with' )
                  ) )

assign_mapper( context, VisualizationRevision, VisualizationRevision.table )

assign_mapper( context, Visualization, Visualization.table,
    properties=dict( user=relation( User ),
                     revisions=relation( VisualizationRevision, backref='visualization',
                                         cascade="all, delete-orphan",
                                         primaryjoin=( Visualization.table.c.id == VisualizationRevision.table.c.visualization_id ) ),
                     latest_revision=relation( VisualizationRevision, post_update=True,
                                               primaryjoin=( Visualization.table.c.latest_revision_id == VisualizationRevision.table.c.id ),
                                               lazy=False ),
                     tags=relation( VisualizationTagAssociation, order_by=VisualizationTagAssociation.table.c.id, backref="visualizations" ),
                     annotations=relation( VisualizationAnnotationAssociation, order_by=VisualizationAnnotationAssociation.table.c.id, backref="visualizations" ),
                     ratings=relation( VisualizationRatingAssociation, order_by=VisualizationRatingAssociation.table.c.id, backref="visualizations" ) 
                   ) )
                   
# Set up proxy so that 
#   Visualization.users_shared_with
# returns a list of users that visualization is shared with.
Visualization.users_shared_with_dot_users = association_proxy( 'users_shared_with', 'user' )
                   
assign_mapper( context, VisualizationUserShareAssociation, VisualizationUserShareAssociation.table,
  properties=dict( user=relation( User, backref='visualizations_shared_by_others' ),
                   visualization=relation( Visualization, backref='users_shared_with' )
                 ) )
                 
# Tag tables.

assign_mapper( context, Tag, Tag.table,
    properties=dict( children=relation(Tag, backref=backref( 'parent', remote_side=[Tag.table.c.id] ) )  
                     ) )

assign_mapper( context, HistoryTagAssociation, HistoryTagAssociation.table,
    properties=dict( tag=relation(Tag, backref="tagged_histories"), user=relation( User ) )
                     )

assign_mapper( context, DatasetTagAssociation, DatasetTagAssociation.table,
    properties=dict( tag=relation(Tag, backref="tagged_datasets"), user=relation( User ) )
                     )

assign_mapper( context, HistoryDatasetAssociationTagAssociation, HistoryDatasetAssociationTagAssociation.table,
    properties=dict( tag=relation(Tag, backref="tagged_history_dataset_associations"), user=relation( User ) )
                     )

assign_mapper( context, PageTagAssociation, PageTagAssociation.table,
    properties=dict( tag=relation(Tag, backref="tagged_pages"), user=relation( User ) )
                    )
                    
assign_mapper( context, StoredWorkflowTagAssociation, StoredWorkflowTagAssociation.table,
    properties=dict( tag=relation(Tag, backref="tagged_workflows"), user=relation( User ) )
                    )
                    
assign_mapper( context, WorkflowStepTagAssociation, WorkflowStepTagAssociation.table,
    properties=dict( tag=relation(Tag, backref="tagged_workflow_steps"), user=relation( User ) )
                    )
                    
assign_mapper( context, VisualizationTagAssociation, VisualizationTagAssociation.table,
    properties=dict( tag=relation(Tag, backref="tagged_visualizations"), user=relation( User ) )
                    )
                    
assign_mapper( context, ToolTagAssociation, ToolTagAssociation.table,
    properties=dict( tag=relation(Tag, backref="tagged_tools"), user=relation( User ) )
                    )
                    
# Annotation tables.
                    
assign_mapper( context, HistoryAnnotationAssociation, HistoryAnnotationAssociation.table,
    properties=dict( history=relation( History ), user=relation( User ) )
                    )
                    
assign_mapper( context, HistoryDatasetAssociationAnnotationAssociation, HistoryDatasetAssociationAnnotationAssociation.table,
    properties=dict( hda=relation( HistoryDatasetAssociation ), user=relation( User ) )
                    )
                    
assign_mapper( context, StoredWorkflowAnnotationAssociation, StoredWorkflowAnnotationAssociation.table,
    properties=dict( stored_workflow=relation( StoredWorkflow ), user=relation( User ) )
                    )

assign_mapper( context, WorkflowStepAnnotationAssociation, WorkflowStepAnnotationAssociation.table,
    properties=dict( workflow_step=relation( WorkflowStep ), user=relation( User ) )
                    )
                    
assign_mapper( context, PageAnnotationAssociation, PageAnnotationAssociation.table,
    properties=dict( page=relation( Page ), user=relation( User ) )
                    )
                    
assign_mapper( context, VisualizationAnnotationAssociation, VisualizationAnnotationAssociation.table,
    properties=dict( visualization=relation( Visualization ), user=relation( User ) )
                    )
                    
# Rating tables.

assign_mapper( context, HistoryRatingAssociation, HistoryRatingAssociation.table,
    properties=dict( history=relation( History ), user=relation( User ) )
                    )
                    
assign_mapper( context, HistoryDatasetAssociationRatingAssociation, HistoryDatasetAssociationRatingAssociation.table,
    properties=dict( hda=relation( HistoryDatasetAssociation ), user=relation( User ) )
                    )
                    
assign_mapper( context, StoredWorkflowRatingAssociation, StoredWorkflowRatingAssociation.table,
    properties=dict( stored_workflow=relation( StoredWorkflow ), user=relation( User ) )
                    )

assign_mapper( context, PageRatingAssociation, PageRatingAssociation.table,
    properties=dict( page=relation( Page ), user=relation( User ) )
                    )

assign_mapper( context, VisualizationRatingAssociation, VisualizationRatingAssociation.table,
    properties=dict( visualization=relation( Visualization ), user=relation( User ) )
                    )

# User tables.
                    
assign_mapper( context, UserPreference, UserPreference.table, 
    properties = {}
              )
              
assign_mapper( context, UserAction, UserAction.table, 
  properties = dict( user=relation( User.mapper ) )
            )

assign_mapper( context, APIKeys, APIKeys.table, 
    properties = {} )
    
# Helper methods.

def db_next_hid( self ):
    """
    Override __next_hid to generate from the database in a concurrency
    safe way.
    """
    conn = object_session( self ).connection()
    table = self.table
    trans = conn.begin()
    try:
        next_hid = select( [table.c.hid_counter], table.c.id == self.id, for_update=True ).scalar()
        table.update( table.c.id == self.id ).execute( hid_counter = ( next_hid + 1 ) )
        trans.commit()
        return next_hid
    except:
        trans.rollback()
        raise

History._next_hid = db_next_hid

def guess_dialect_for_url( url ):
    return (url.split(':', 1))[0]

def load_egg_for_url( url ):
    # Load the appropriate db module
    dialect = guess_dialect_for_url( url )
    try:
        egg = dialect_to_egg[dialect]
        try:
            pkg_resources.require( egg )
            log.debug( "%s egg successfully loaded for %s dialect" % ( egg, dialect ) )
        except:
            # If the module's in the path elsewhere (i.e. non-egg), it'll still load.
            log.warning( "%s egg not found, but an attempt will be made to use %s anyway" % ( egg, dialect ) )
    except KeyError:
        # Let this go, it could possibly work with db's we don't support
        log.error( "database_connection contains an unknown SQLAlchemy database dialect: %s" % dialect )

def init( file_path, url, engine_options={}, create_tables=False, database_query_profiling_proxy=False, object_store=None ):
    """Connect mappings to the database"""
    # Connect dataset to the file path
    Dataset.file_path = file_path
    # Connect dataset to object store
    Dataset.object_store = object_store
    # Load the appropriate db module
    load_egg_for_url( url )
    # Should we use the logging proxy?
    if database_query_profiling_proxy:
        import galaxy.model.orm.logging_connection_proxy as logging_connection_proxy
        proxy = logging_connection_proxy.LoggingProxy()
    else:
        proxy = None
    # Create the database engine
    engine = create_engine( url, proxy=proxy, **engine_options )
    # Connect the metadata to the database.
    metadata.bind = engine
    # Clear any existing contextual sessions and reconfigure
    Session.remove()
    Session.configure( bind=engine )
    # Create tables if needed
    if create_tables:
        metadata.create_all()
        # metadata.engine.commit()
    # Pack everything into a bunch
    result = Bunch( **globals() )
    result.engine = engine
    # model.flush() has been removed.
    result.session = Session
    # For backward compatibility with "model.context.current"
    result.context = Session
    result.create_tables = create_tables
    #load local galaxy security policy
    result.security_agent = GalaxyRBACAgent( result )
    return result
    
def get_suite():
    """Get unittest suite for this module"""
    import unittest, mapping_tests
    return unittest.makeSuite( mapping_tests.MappingTests )