Changeset 5730
- Timestamp:
- Sep 12, 2011, 12:42:14 PM (11 years ago)
- Location:
- trunk/src
- Files:
-
- 4 edited
Legend:
- Unmodified
- Added
- Removed
-
trunk/src/core/common-queries.xml
r5703 r5730 2865 2865 </sql> 2866 2866 <description> 2867 An SQL query that drops a column from a table. 2867 An SQL query that drops a column (2) from a table (1). 2868 </description> 2869 </query> 2870 2871 <query id="DROP_UNIQUE_INDEX" type="SQL"> 2872 <sql> 2873 DROP INDEX [{2}] ON [{1}] 2874 </sql> 2875 <description> 2876 An SQL query that drops a unique index (2) from a table (1). 2868 2877 </description> 2869 2878 </query> … … 2874 2883 </sql> 2875 2884 <description> 2876 An SQL query that drops a foreign key from a table.2885 An SQL query that drops a foreign key (2) from a table (1). 2877 2886 </description> 2878 2887 </query> … … 2883 2892 </sql> 2884 2893 <description> 2885 An SQL query that drops a table .2894 An SQL query that drops a table (1). 2886 2895 </description> 2887 2896 </query> … … 2944 2953 </description> 2945 2954 </query> 2955 2956 <query id="GET_IMAGES" type="SQL"> 2957 <sql> 2958 SELECT 2959 [id], [version], 2960 [file_id] 2961 FROM 2962 [Images] 2963 WHERE [scan_id] = :scanId 2964 </sql> 2965 <description> 2966 An SQL query that load BASE 2.17 image files for a given scan. 2967 </description> 2968 </query> 2969 2970 <query id="GET_PROTOCOLTYPES" type="SQL"> 2971 <sql> 2972 SELECT 2973 [id], [version], 2974 [entry_date], [name], 2975 [description], [removed], 2976 [system_id] 2977 FROM 2978 [ProtocolTypes] 2979 </sql> 2980 <description> 2981 An SQL query that load all BASE 2.17 protocol types. 2982 </description> 2983 </query> 2984 2985 <query id="GET_HARDWARETYPES" type="SQL"> 2986 <sql> 2987 SELECT 2988 [id], [version], 2989 [entry_date], [name], 2990 [description], [removed], 2991 [system_id] 2992 FROM 2993 [HardwareTypes] 2994 </sql> 2995 <description> 2996 An SQL query that load all BASE 2.17 hardware types. 2997 </description> 2998 </query> 2999 3000 <query id="GET_SOFTWARETYPES" type="SQL"> 3001 <sql> 3002 SELECT 3003 [id], [version], 3004 [entry_date], [name], 3005 [description], [system_id] 3006 FROM 3007 [SoftwareTypes] 3008 </sql> 3009 <description> 3010 An SQL query that load all BASE 2.17 software types. 3011 </description> 3012 </query> 3013 3014 <query id="GET_FILETYPES" type="SQL"> 3015 <sql> 3016 SELECT 3017 [id], [version], 3018 [entry_date], [name], 3019 [description], [system_id] 3020 FROM 3021 [FileTypes] 3022 </sql> 3023 <description> 3024 An SQL query that load all BASE 2.17 file types. 3025 </description> 3026 </query> 3027 2946 3028 2947 3029 <query id="COPY_BIOMATERIALEVENTSOURCES" type="SQL"> … … 2960 3042 </sql> 2961 3043 <description> 2962 An SQL query that load all BASE 2.17 biomaterial 2963 event sources. 2964 </description> 2965 </query> 2966 2967 <query id="SET_PARENTTYPE_ON_BIOMATERIALS_WITH_ONE_PARENT" type="SQL"> 2968 <sql> 2969 UPDATE [BioMaterials] 2970 SET [parent_type]= 2971 CASE [discriminator] 2972 WHEN 2 THEN 201 2973 WHEN 3 THEN 202 2974 WHEN 4 THEN 203 2975 END 2976 WHERE [parent_id] IS NOT NULL 2977 </sql> 2978 <description> 2979 An SQL query that sets the subtype of labeled extracts 3044 An SQL query that load copy BASE 2.17 biomaterial 3045 event sources to the new table. 3046 </description> 3047 </query> 3048 3049 <query id="COPY_SAMPLE_BIOSOURCE_PARENT_LINK" type="SQL"> 3050 <sql> 3051 INSERT INTO [BioMaterialEventSources2] 3052 ( 3053 [version], 3054 [biomaterial_id], [event_id], 3055 [used_quantity], [position] 3056 ) 3057 SELECT 3058 0, 3059 bm.[parent_id], evt.[id], 3060 null, 1 3061 FROM [BioMaterials] bm 3062 INNER JOIN [BioMaterialEvents] evt ON bm.[id]=evt.[biomaterial_id] AND evt.[event_type]=1 3063 WHERE bm.[discriminator] = 2 AND NOT bm.[parent_id] IS NULL 3064 </sql> 3065 <description> 3066 An SQL query that copy Sample--BioSource parent 3067 links to the BioMaterialEventSources2 table. 3068 </description> 3069 </query> 3070 3071 <query id="NULLIFY_ALL_BIOMATERIAL_PARENTS" type="SQL"> 3072 <sql> 3073 UPDATE [BioMaterials] 3074 SET [parent_type] = NULL, [parent_id] = NULL 3075 </sql> 3076 <description> 3077 An SQL query that nullify all biomaterial parent 3078 information. 3079 </description> 3080 </query> 3081 3082 <query id="GET_BIOMATERIAL_PARENT_INFO" type="SQL"> 3083 <sql> 3084 SELECT bm.[id], bm.[discriminator], evt.[biomaterial_id] 3085 FROM [BioMaterialEventSources2] bm2 3086 INNER JOIN [BioMaterialEvents] evt ON bm2.[event_id]=evt.[id] AND evt.[event_type]=1 3087 INNER JOIN [BioMaterials] bm ON bm2.[biomaterial_id]=bm.[id] 3088 ORDER BY evt.[biomaterial_id] 3089 </sql> 3090 <description> 3091 An SQL query that get parent biomaterial information. We need 3092 parent_id, parent_type and child_id ordered by child_id 3093 </description> 3094 </query> 3095 3096 3097 <query id="SET_BIOMATERIAL_PARENT" type="SQL"> 3098 <sql> 3099 UPDATE [BioMaterials] 3100 SET [parent_type] = :parentType, [parent_id] = :parentId 3101 WHERE [id] = :childId 3102 </sql> 3103 <description> 3104 An SQL query that set the biomaterial parent info for a single 3105 biomaterial. 2980 3106 </description> 2981 3107 </query> … … 3087 3213 </query> 3088 3214 3215 <query id="UPDATE_SUBTYPEID_FROM_OLDTYPEID" type="SQL"> 3216 <sql> 3217 UPDATE [{1}] 3218 SET [{2}] = :subtypeId 3219 WHERE [{3}] = :oldTypeId 3220 </sql> 3221 <description> 3222 An SQL query that update the subtype_id column on 3223 a generic table. 3224 </description> 3225 </query> 3226 3227 3089 3228 <query id="DELETE_PLUGINGUICONTEXTS_ITEMTYPE" type="SQL"> 3090 3229 <sql> … … 3131 3270 </query> 3132 3271 3272 <query id="UPDATE_FILESETMEMBER_FILETYPE" type="HQL"> 3273 <sql> 3274 UPDATE FileSetMemberData mbr 3275 SET mbr.dataFileType = :newType 3276 WHERE mbr.dataFileType = :oldType 3277 </sql> 3278 <description> 3279 A Hibernate query that update the data file type of all 3280 file set members with a given old data file type. 3281 </description> 3282 </query> 3283 3133 3284 </predefined-queries> -
trunk/src/core/net/sf/basedb/core/Item.java
r5717 r5730 157 157 FILE(81, "File", "fle", File.class, FileData.class, DefinedPermissions.shareable, 158 158 1100), 159 160 /* 161 IMPORTANT! Do not use the following codes since they were used in BASE 2: 162 82 = FILETYPE 163 */ 159 164 160 165 /** … … 179 184 PROTOCOL(101, "Protocol", "prl", Protocol.class, ProtocolData.class, DefinedPermissions.shareable, 180 185 740), 186 /* 187 IMPORTANT! Do not use the following codes since they were used in BASE 2: 188 102 = PROTOCOLTYPE 189 122 = HARDWARETYPE 190 124 = SOFTWARETYPE 191 */ 181 192 182 193 /** -
trunk/src/core/net/sf/basedb/core/Update.java
r5703 r5730 30 30 import java.util.Date; 31 31 import java.util.HashMap; 32 import java.util.HashSet; 32 33 import java.util.Iterator; 33 34 import java.util.List; … … 40 41 import net.sf.basedb.core.data.ArraySlideData; 41 42 import net.sf.basedb.core.data.ContextData; 43 import net.sf.basedb.core.data.DataFileTypeData; 42 44 import net.sf.basedb.core.data.DerivedBioAssayData; 45 import net.sf.basedb.core.data.FileData; 46 import net.sf.basedb.core.data.FileSetData; 47 import net.sf.basedb.core.data.FileSetMemberData; 43 48 import net.sf.basedb.core.data.HardwareData; 44 49 import net.sf.basedb.core.data.ItemKeyData; 50 import net.sf.basedb.core.data.ItemSubtypeData; 45 51 import net.sf.basedb.core.data.PhysicalBioAssayData; 46 52 import net.sf.basedb.core.data.ProjectKeyData; … … 309 315 HibernateUtil.executeUpdate(query); 310 316 317 // All file set members for derived bioassays are microarray images 318 query = HibernateUtil.getPredefinedQuery(session, "GET_DATAFILETYPE_FOR_EXTERNAL_ID"); 319 query.setString("externalId", "tmp.image"); 320 DataFileTypeData tmpType = HibernateUtil.loadData(DataFileTypeData.class, query); 321 query.setString("externalId", DataFileType.MICROARRAY_IMAGE); 322 DataFileTypeData imgType = HibernateUtil.loadData(DataFileTypeData.class, query); 323 query = HibernateUtil.getPredefinedQuery(session, "UPDATE_FILESETMEMBER_FILETYPE"); 324 // UPDATE FileSetMemberData mbr SET mbr.dataFileType = :newType WHERE mbr.dataFileType = :oldType 325 query.setInteger("newType", imgType.getId()); 326 query.setInteger("oldType", tmpType.getId()); 327 query.executeUpdate(); 328 329 // Remove the temporary data file type 330 session.delete(tmpType); 331 311 332 // Update the shcema version number 312 333 setSchemaVersion(session, schemaVersion); … … 325 346 } 326 347 348 // Item type codes for item types that was removed since BASE 2 349 private static final int LABELEDEXTRACT = 204; 350 private static final int HYBRIDIZATION = 261; 351 private static final int SCAN = 262; 352 private static final int IMAGE = 263; 353 private static final int PROTOCOLTYPE = 102; 354 private static final int HARDWARETYPE = 122; 355 private static final int SOFTWARETYPE = 124; 356 private static final int FILETYPE = 82; 357 358 public static void main(String[] args) 359 { 360 org.hibernate.Session session = null; 361 Config.setProperty("extensions.disabled", "true"); 362 Application.start(false, false, false); 363 session = HibernateUtil.newSession(); 364 cleanContextFromProperty(session, Item.SOFTWARE, "softwareType", "itemSubtype", true); 365 session.close(); 366 Application.stop(); 367 } 327 368 328 369 public static synchronized void updateToBase3(ProgressReporter progress, String rootLogin, String rootPassword) … … 354 395 copyJarPathToJarFile(session, "PluginDefinitions"); 355 396 copyJarPathToJarFile(session, "PluginTypes"); 356 cleanContextFromProperty(session, Item.PLUGINDEFINITION, "jarPath", "jarFile" );357 cleanContextFromProperty(session, Item.PLUGINTYPE, "jarPath", "jarFile" );397 cleanContextFromProperty(session, Item.PLUGINDEFINITION, "jarPath", "jarFile", false); 398 cleanContextFromProperty(session, Item.PLUGINTYPE, "jarPath", "jarFile", false); 358 399 359 400 // #1598: Four properties removed from DataFileType 360 cleanContextFromProperty(session, Item.DATAFILETYPE, "validatorClass", null); 361 cleanContextFromProperty(session, Item.DATAFILETYPE, "validatorJarPath", null); 362 cleanContextFromProperty(session, Item.DATAFILETYPE, "metadataReaderClass", null); 363 cleanContextFromProperty(session, Item.DATAFILETYPE, "metadataReaderJarPath", null); 364 401 cleanContextFromProperty(session, Item.DATAFILETYPE, "validatorClass", null, true); 402 cleanContextFromProperty(session, Item.DATAFILETYPE, "validatorJarPath", null, true); 403 cleanContextFromProperty(session, Item.DATAFILETYPE, "metadataReaderClass", null, true); 404 cleanContextFromProperty(session, Item.DATAFILETYPE, "metadataReaderJarPath", null, true); 405 406 // #1604 407 progress.display(15, "--Removing unique constraint on FileSetMembers"); 408 dropUniqueIndex(session, "FileSetMembers", "fileset_id", "datafiletype_id"); 409 365 410 // #1153 366 411 progress.display(20, "--Re-linking parent biomaterials"); 367 412 fixBioMaterialParents(session); 368 413 369 progress.display( 30, "--Converting labeled extracts to extracts");414 progress.display(25, "--Converting labeled extracts to extracts"); 370 415 fixLabeledExtracts(session); 371 372 progress.display(40, "--Converting hybridizations to bioassays"); 416 removeContext(session, LABELEDEXTRACT); 417 418 progress.display(30, "--Converting hybridizations to bioassays"); 373 419 Map<Integer, Integer> hybMap = copyHybridizations(session); 374 375 progress.display(50, "--Converting scans to bioassays"); 420 removeContext(session, HYBRIDIZATION); 421 cleanContextFromProperty(session, Item.ARRAYSLIDE, "hybridization", "physicalBioAssay", false); 422 423 progress.display(35, "--Converting scans to bioassays"); 376 424 Map<Integer, Integer> scanMap = copyScans(session, hybMap); 377 378 progress.display(60, "--Re-linking rawbioassays to parents"); 425 removeContext(session, SCAN); 426 removeContext(session, IMAGE); 427 428 progress.display(40, "--Re-linking rawbioassays to parents"); 379 429 //fixRawBioAssays(session); 380 430 431 // #1597 432 progress.display(45, "--Converting protocol types to item subtypes"); 433 copyProtocolTypes(session); 434 cleanContextFromProperty(session, Item.PROTOCOL, "protocolType", "itemSubtype", true); 435 cleanContextFromProperty(session, Item.PLATEEVENTTYPE, "protocolType", "protocolType", true); 436 removeContext(session, PROTOCOLTYPE); 437 438 progress.display(50, "--Converting software types to item subtypes"); 439 copySoftwareTypes(session); 440 cleanContextFromProperty(session, Item.SOFTWARE, "softwareType", "itemSubtype", true); 441 removeContext(session, SOFTWARETYPE); 442 443 progress.display(55, "--Converting hardware types to item subtypes"); 444 copyHardwareTypes(session); 445 cleanContextFromProperty(session, Item.HARDWARE, "hardwareType", "itemSubtype", true); 446 removeContext(session, HARDWARETYPE); 447 448 progress.display(60, "--Converting file types to item subtypes"); 449 copyFileTypes(session); 450 cleanContextFromProperty(session, Item.FILE, "fileType", "itemSubtype", true); 451 cleanContextFromProperty(session, Item.MIMETYPE, "fileType", "fileType", true); 452 cleanContextFromProperty(session, Item.DATAFILETYPE, "genericType", "genericType", true); 453 removeContext(session, FILETYPE); 454 381 455 // 2: remove unused columns/tables 382 progress.display( 70, "--Dropping old database objects");456 progress.display(80, "--Dropping old database objects"); 383 457 // #1592 384 458 dropColumn(session, "PluginDefinitions", "jar_path", progress); … … 530 604 } 531 605 606 private static void dropUniqueIndex(org.hibernate.Session session, String tableName, String... columnNames) 607 throws SQLException 608 { 609 org.hibernate.Transaction tx = null; 610 org.hibernate.Query query = null; 611 try 612 { 613 log.debug("Dropping index: " + tableName + ".[" + Arrays.toString(columnNames) + "]"); 614 tx = HibernateUtil.newTransaction(session); 615 616 // Drop foreign keys where this column is used 617 TableInfo info = getTableInfo(session, tableName); 618 String indexName = info.findIndexName(null, new HashSet<String>(Arrays.asList(columnNames))); 619 if (indexName != null) 620 { 621 query = HibernateUtil.getPredefinedSQLQuery(session, "DROP_UNIQUE_INDEX", tableName, indexName); 622 query.executeUpdate(); 623 } 624 625 HibernateUtil.commit(tx); 626 } 627 catch (BaseException ex) 628 { 629 if (tx != null) HibernateUtil.rollback(tx); 630 throw ex; 631 } 632 } 633 634 532 635 private static void dropTable(org.hibernate.Session session, String tableName, ProgressReporter progress) 533 636 { … … 550 653 } 551 654 552 553 private static void cleanContextFromProperty(org.hibernate.Session session, Item itemType, String propertyName, String replacementPropertyName) 655 private static void removeContext(org.hibernate.Session session, int itemType) 656 { 657 org.hibernate.Transaction tx = null; 658 org.hibernate.Query query = null; 659 try 660 { 661 log.debug("Removing context for item: " + itemType); 662 tx = HibernateUtil.newTransaction(session); 663 664 query = HibernateUtil.createQuery(session, "SELECT ctx FROM ContextData ctx WHERE ctx.itemType = :itemType"); 665 query.setInteger("itemType", itemType); 666 for (ContextData ctx : HibernateUtil.loadList(ContextData.class, query, null)) 667 { 668 session.delete(ctx); 669 } 670 671 HibernateUtil.commit(tx); 672 } 673 catch (BaseException ex) 674 { 675 if (tx != null) HibernateUtil.rollback(tx); 676 throw ex; 677 } 678 } 679 680 private static void cleanContextFromProperty(org.hibernate.Session session, Item itemType, 681 String propertyName, String replacementPropertyName, boolean forceRemoveFilter) 554 682 { 555 683 org.hibernate.Transaction tx = null; … … 586 714 } 587 715 } 588 716 589 717 // Filter 590 718 Map<String, PropertyFilterData> filters = ctx.getPropertyFilters(); 591 719 PropertyFilterData propertyFilter = filters.remove(propertyName); 592 if (propertyFilter != null && replacementPropertyName != null )720 if (propertyFilter != null && replacementPropertyName != null && !forceRemoveFilter) 593 721 { 594 722 // Replace with new property … … 614 742 List<String> elements = new ArrayList<String>(Arrays.asList(list.split(","))); 615 743 String result = list; 616 int pos = elements.indexOf(find); 617 if (pos >= 0) 618 { 619 if (replacement != null) 620 { 621 elements.set(pos, replacement); 622 } 623 else 624 { 625 elements.remove(pos); 626 } 744 745 Iterator<String> it = elements.iterator(); 746 boolean hasMatched = false; 747 int index = 0; 748 while (it.hasNext()) 749 { 750 String value = it.next(); 751 if (value != null && value.startsWith(find)) 752 { 753 // A match, replace or remove 754 hasMatched = true; 755 if (replacement != null) 756 { 757 elements.set(index, replacement + value.substring(find.length())); 758 } 759 else 760 { 761 it.remove(); 762 } 763 } 764 } 765 if (hasMatched) 766 { 627 767 if (elements.size() == 0) 628 768 { … … 654 794 query.executeUpdate(); 655 795 656 fixSoftLinks(session, 204, Item.EXTRACT.getValue(),796 fixSoftLinks(session, LABELEDEXTRACT, Item.EXTRACT.getValue(), 657 797 "net.sf.basedb.core.data.LabeledExtractData", 658 798 "net.sf.basedb.core.data.ExtractData", … … 712 852 } 713 853 714 fixSoftLinks(session, 261, Item.PHYSICALBIOASSAY.getValue(),854 fixSoftLinks(session, HYBRIDIZATION, Item.PHYSICALBIOASSAY.getValue(), 715 855 "net.sf.basedb.core.data.HybridizationData", 716 856 "net.sf.basedb.core.data.PhysicalBioAssayData", … … 744 884 { 745 885 tx = HibernateUtil.newTransaction(session); 886 DataFileTypeData fileType = null; 746 887 org.hibernate.Query scanQuery = 747 888 HibernateUtil.getPredefinedSQLQuery(session, "GET_SCANS"); 889 org.hibernate.Query imageQuery = 890 HibernateUtil.getPredefinedSQLQuery(session, "GET_IMAGES"); 748 891 Iterator it = scanQuery.list().iterator(); 749 892 while (it.hasNext()) … … 765 908 766 909 session.save(bioAssay); 767 768 910 idMap.put(scanId, bioAssay.getId()); 769 } 770 771 fixSoftLinks(session, 262, Item.DERIVEDBIOASSAY.getValue(), 911 912 // Images 913 imageQuery.setInteger("scanId", scanId); 914 Iterator it2 = imageQuery.list().iterator(); 915 FileSetData fileSet = null; 916 917 while (it2.hasNext()) 918 { 919 Object[] imgRow = (Object[])it2.next(); 920 921 if (fileSet == null) 922 { 923 fileSet = new FileSetData(); 924 fileSet.setItemType(Item.DERIVEDBIOASSAY.getValue()); 925 session.save(fileSet); 926 bioAssay.setFileSet(fileSet); 927 } 928 if (fileType == null) 929 { 930 // Create a temporary file type, which will be removed 931 // once the install has created the correct one 932 fileType = new DataFileTypeData(); 933 fileType.setName("Temporary file type for scanned images"); 934 fileType.setExternalId("tmp.image"); 935 fileType.setItemType(Item.DERIVEDBIOASSAY.getValue()); 936 session.save(fileType); 937 } 938 939 FileSetMemberData member = new FileSetMemberData(); 940 member.setFile(load(session, FileData.class, imgRow[2])); 941 member.setFileSet(fileSet); 942 member.setDataFileType(fileType); 943 session.save(member); 944 } 945 946 } 947 948 fixSoftLinks(session, SCAN, Item.DERIVEDBIOASSAY.getValue(), 772 949 "net.sf.basedb.core.data.ScanData", 773 950 "net.sf.basedb.core.data.DerivedBioAssayData", … … 785 962 786 963 /** 964 Copy from 'ProtocolTypes' table to 'ItemSubtypes' table. Fix 965 "hard links" in 'Protocols' and 'PlateEventTypes' tables. 966 Fix all "soft links" related to "PROTOCOLTYPE", so that they 967 now use "ITEMSUBTYPE" instead. 968 @return A Map with oldId -> newId for protocoltypes 969 */ 970 private static Map<Integer, Integer> copyProtocolTypes(org.hibernate.Session session) 971 { 972 org.hibernate.Transaction tx = null; 973 Map<Integer, Integer> idMap = new HashMap<Integer, Integer>(); 974 try 975 { 976 tx = HibernateUtil.newTransaction(session); 977 org.hibernate.Query typeQuery = 978 HibernateUtil.getPredefinedSQLQuery(session, "GET_PROTOCOLTYPES"); 979 org.hibernate.Query protocolQuery = HibernateUtil.getPredefinedSQLQuery(session, 980 "UPDATE_SUBTYPEID_FROM_OLDTYPEID", "Protocols", "subtype_id", "protocoltype_id"); 981 // UPDATE Protocols SET subtype_id = ? WHERE protocoltype_id=? 982 org.hibernate.Query plateEventTypesQuery = HibernateUtil.getPredefinedSQLQuery(session, 983 "UPDATE_SUBTYPEID_FROM_OLDTYPEID", "PlateEventTypes", "protocolsubtype_id", "protocoltype_id"); 984 // UPDATE PlateEventTypes SET protocolsubtype_id = ? WHERE protocoltype_id=? 985 Iterator it = typeQuery.list().iterator(); 986 while (it.hasNext()) 987 { 988 Object[] row = (Object[])it.next(); 989 Integer typeId = (Integer)row[0]; 990 ItemSubtypeData subtype = new ItemSubtypeData(); 991 subtype.setItemType(Item.PROTOCOL.getValue()); 992 subtype.setEntryDate((Date)row[2]); 993 subtype.setName((String)row[3]); 994 subtype.setDescription((String)row[4]); 995 subtype.setRemoved((Boolean)row[5]); 996 subtype.setSystemId((String)row[6]); 997 session.save(subtype); 998 999 idMap.put(typeId, subtype.getId()); 1000 1001 // Protocols.subtype_id 1002 protocolQuery.setInteger("subtypeId", subtype.getId()); 1003 protocolQuery.setInteger("oldTypeId", typeId); 1004 protocolQuery.executeUpdate(); 1005 1006 // PlateEventTypes.subtype_id 1007 plateEventTypesQuery.setInteger("subtypeId", subtype.getId()); 1008 plateEventTypesQuery.setInteger("oldTypeId", typeId); 1009 plateEventTypesQuery.executeUpdate(); 1010 } 1011 1012 fixSoftLinks(session, PROTOCOLTYPE, Item.ITEMSUBTYPE.getValue(), 1013 "net.sf.basedb.core.data.ProtocolTypeData", 1014 "net.sf.basedb.core.data.ItemSubtypeData", 1015 idMap); 1016 1017 HibernateUtil.commit(tx); 1018 } 1019 catch (BaseException ex) 1020 { 1021 if (tx != null) HibernateUtil.rollback(tx); 1022 throw ex; 1023 } 1024 return idMap; 1025 } 1026 1027 /** 1028 Copy from 'HardwareTypes' table to 'ItemSubtypes' table. Fix 1029 "hard links" in 'Hardware' table. 1030 Fix all "soft links" related to "HARDWARETYPE", so that they 1031 now use "ITEMSUBTYPE" instead. 1032 @return A Map with oldId -> newId for hardwaretypes 1033 */ 1034 private static Map<Integer, Integer> copyHardwareTypes(org.hibernate.Session session) 1035 { 1036 org.hibernate.Transaction tx = null; 1037 Map<Integer, Integer> idMap = new HashMap<Integer, Integer>(); 1038 try 1039 { 1040 tx = HibernateUtil.newTransaction(session); 1041 org.hibernate.Query typeQuery = 1042 HibernateUtil.getPredefinedSQLQuery(session, "GET_HARDWARETYPES"); 1043 org.hibernate.Query hardwareQuery = HibernateUtil.getPredefinedSQLQuery(session, 1044 "UPDATE_SUBTYPEID_FROM_OLDTYPEID", "Hardware", "subtype_id", "hardwaretype_id"); 1045 // UPDATE Hardware SET subtype_id = ? WHERE hardwaretype_id=? 1046 Iterator it = typeQuery.list().iterator(); 1047 while (it.hasNext()) 1048 { 1049 Object[] row = (Object[])it.next(); 1050 Integer typeId = (Integer)row[0]; 1051 ItemSubtypeData subtype = new ItemSubtypeData(); 1052 subtype.setItemType(Item.HARDWARE.getValue()); 1053 subtype.setEntryDate((Date)row[2]); 1054 subtype.setName((String)row[3]); 1055 subtype.setDescription((String)row[4]); 1056 subtype.setRemoved((Boolean)row[5]); 1057 subtype.setSystemId((String)row[6]); 1058 session.save(subtype); 1059 1060 idMap.put(typeId, subtype.getId()); 1061 1062 // Hardware.subtype_id 1063 hardwareQuery.setInteger("subtypeId", subtype.getId()); 1064 hardwareQuery.setInteger("oldTypeId", typeId); 1065 hardwareQuery.executeUpdate(); 1066 } 1067 1068 fixSoftLinks(session, HARDWARETYPE, Item.ITEMSUBTYPE.getValue(), 1069 "net.sf.basedb.core.data.HardwareTypeData", 1070 "net.sf.basedb.core.data.ItemSubtypeData", 1071 idMap); 1072 1073 HibernateUtil.commit(tx); 1074 } 1075 catch (BaseException ex) 1076 { 1077 if (tx != null) HibernateUtil.rollback(tx); 1078 throw ex; 1079 } 1080 return idMap; 1081 } 1082 1083 /** 1084 Copy from 'FileTypes' table to 'ItemSubtypes' table. Fix 1085 "hard links" in 'Files', 'MimeTypes' and 'DataFileTypes' tables. 1086 Fix all "soft links" related to "FILETYPE", so that they 1087 now use "ITEMSUBTYPE" instead. 1088 @return A Map with oldId -> newId for file types 1089 */ 1090 private static Map<Integer, Integer> copyFileTypes(org.hibernate.Session session) 1091 { 1092 org.hibernate.Transaction tx = null; 1093 Map<Integer, Integer> idMap = new HashMap<Integer, Integer>(); 1094 try 1095 { 1096 tx = HibernateUtil.newTransaction(session); 1097 org.hibernate.Query typeQuery = 1098 HibernateUtil.getPredefinedSQLQuery(session, "GET_FILETYPES"); 1099 org.hibernate.Query fileQuery = HibernateUtil.getPredefinedSQLQuery(session, 1100 "UPDATE_SUBTYPEID_FROM_OLDTYPEID", "Files", "subtype_id", "filetype_id"); 1101 // UPDATE Files SET subtype_id = ? WHERE filetype_id=? 1102 org.hibernate.Query mimeTypeQuery = HibernateUtil.getPredefinedSQLQuery(session, 1103 "UPDATE_SUBTYPEID_FROM_OLDTYPEID", "MimeTypes", "filesubtype_id", "filetype_id"); 1104 // UPDATE MimeTypes SET filesubtype_id = ? WHERE filetype_id=? 1105 org.hibernate.Query dataFileTypeQuery = HibernateUtil.getPredefinedSQLQuery(session, 1106 "UPDATE_SUBTYPEID_FROM_OLDTYPEID", "DataFileTypes", "filesubtype_id", "filetype_id"); 1107 // UPDATE DataFileTypes SET filesubtype_id = ? WHERE filetype_id=? 1108 Iterator it = typeQuery.list().iterator(); 1109 while (it.hasNext()) 1110 { 1111 Object[] row = (Object[])it.next(); 1112 Integer typeId = (Integer)row[0]; 1113 ItemSubtypeData subtype = new ItemSubtypeData(); 1114 subtype.setItemType(Item.FILE.getValue()); 1115 subtype.setEntryDate((Date)row[2]); 1116 subtype.setName((String)row[3]); 1117 subtype.setDescription((String)row[4]); 1118 subtype.setSystemId((String)row[5]); 1119 session.save(subtype); 1120 1121 idMap.put(typeId, subtype.getId()); 1122 1123 // Files.subtype_id 1124 fileQuery.setInteger("subtypeId", subtype.getId()); 1125 fileQuery.setInteger("oldTypeId", typeId); 1126 fileQuery.executeUpdate(); 1127 1128 // MimeTypes.subtype_id 1129 mimeTypeQuery.setInteger("subtypeId", subtype.getId()); 1130 mimeTypeQuery.setInteger("oldTypeId", typeId); 1131 mimeTypeQuery.executeUpdate(); 1132 1133 // DataFileTypes.subtype_id 1134 dataFileTypeQuery.setInteger("subtypeId", subtype.getId()); 1135 dataFileTypeQuery.setInteger("oldTypeId", typeId); 1136 dataFileTypeQuery.executeUpdate(); 1137 } 1138 1139 fixSoftLinks(session, FILETYPE, Item.ITEMSUBTYPE.getValue(), 1140 "net.sf.basedb.core.data.FileTypeData", 1141 "net.sf.basedb.core.data.ItemSubtypeData", 1142 idMap); 1143 1144 HibernateUtil.commit(tx); 1145 } 1146 catch (BaseException ex) 1147 { 1148 if (tx != null) HibernateUtil.rollback(tx); 1149 throw ex; 1150 } 1151 return idMap; 1152 } 1153 1154 1155 /** 1156 Copy from 'SoftwareTypes' table to 'ItemSubtypes' table. Fix 1157 "hard links" in 'Software' table. 1158 Fix all "soft links" related to "SOFTWARETYPE", so that they 1159 now use "ITEMSUBTYPE" instead. 1160 @return A Map with oldId -> newId for softwaretypes 1161 */ 1162 private static Map<Integer, Integer> copySoftwareTypes(org.hibernate.Session session) 1163 { 1164 org.hibernate.Transaction tx = null; 1165 Map<Integer, Integer> idMap = new HashMap<Integer, Integer>(); 1166 try 1167 { 1168 tx = HibernateUtil.newTransaction(session); 1169 org.hibernate.Query typeQuery = 1170 HibernateUtil.getPredefinedSQLQuery(session, "GET_SOFTWARETYPES"); 1171 org.hibernate.Query softwareQuery = HibernateUtil.getPredefinedSQLQuery(session, 1172 "UPDATE_SUBTYPEID_FROM_OLDTYPEID", "Software", "subtype_id", "softwaretype_id"); 1173 // UPDATE Software SET subtype_id = ? WHERE softwaretype_id=? 1174 Iterator it = typeQuery.list().iterator(); 1175 while (it.hasNext()) 1176 { 1177 Object[] row = (Object[])it.next(); 1178 Integer typeId = (Integer)row[0]; 1179 ItemSubtypeData subtype = new ItemSubtypeData(); 1180 subtype.setItemType(Item.SOFTWARE.getValue()); 1181 subtype.setEntryDate((Date)row[2]); 1182 subtype.setName((String)row[3]); 1183 subtype.setDescription((String)row[4]); 1184 subtype.setSystemId((String)row[5]); 1185 session.save(subtype); 1186 1187 idMap.put(typeId, subtype.getId()); 1188 1189 // Hardware.subtype_id 1190 softwareQuery.setInteger("subtypeId", subtype.getId()); 1191 softwareQuery.setInteger("oldTypeId", typeId); 1192 softwareQuery.executeUpdate(); 1193 } 1194 1195 fixSoftLinks(session, SOFTWARETYPE, Item.ITEMSUBTYPE.getValue(), 1196 "net.sf.basedb.core.data.SoftwareTypeData", 1197 "net.sf.basedb.core.data.ItemSubtypeData", 1198 idMap); 1199 1200 HibernateUtil.commit(tx); 1201 } 1202 catch (BaseException ex) 1203 { 1204 if (tx != null) HibernateUtil.rollback(tx); 1205 throw ex; 1206 } 1207 return idMap; 1208 } 1209 1210 1211 /** 787 1212 Copy from 'BioMaterialEventSources' to 'BioMaterialEventSources2' 788 1213 table. … … 798 1223 tx = HibernateUtil.newTransaction(session); 799 1224 800 // Copy to BioMaterialEventSources2 1225 /* 1226 The BioMaterialEventSources table contains parent 1227 links for all biomaterials except sample->biosource 1228 which is in the BioMaterials.parent_id column only. 1229 The update need to make sure that ALL parent 1230 links are in the BioMaterialEventSources2 table. Items 1231 with a single parent should have the BioMaterial.parent_id 1232 set and BioMaterial.parent_type must also be set if there is 1233 at least one parent and should be null if there are no parents. 1234 */ 1235 1236 // Copy from BioMaterialEventSoruces to BioMaterialEventSources2 801 1237 org.hibernate.Query query = HibernateUtil.getPredefinedSQLQuery(session, 802 1238 "COPY_BIOMATERIALEVENTSOURCES"); 803 1239 query.executeUpdate(); 804 1240 1241 // Copy from BioMaterial.parent_id to BioMaterialEventSources2 for Sample->BioSource 805 1242 query = HibernateUtil.getPredefinedSQLQuery(session, 806 "SET_PARENTTYPE_ON_BIOMATERIALS_WITH_ONE_PARENT");1243 "COPY_SAMPLE_BIOSOURCE_PARENT_LINK"); 807 1244 query.executeUpdate(); 1245 1246 // Nullify parent_id and parent type for all biomaterials 1247 query = HibernateUtil.getPredefinedSQLQuery(session, "NULLIFY_ALL_BIOMATERIAL_PARENTS"); 1248 query.executeUpdate(); 1249 1250 // Get all parent info from BioMaterialEventSources2 1251 query = HibernateUtil.getPredefinedSQLQuery(session, "GET_BIOMATERIAL_PARENT_INFO"); 1252 org.hibernate.Query setQuery = HibernateUtil.getPredefinedSQLQuery(session, 1253 "SET_BIOMATERIAL_PARENT"); 1254 Iterator it = query.list().iterator(); 1255 Integer lastChildId = null; 1256 while (it.hasNext()) 1257 { 1258 Object[] row = (Object[])it.next(); 1259 1260 Integer parentId = (Integer)row[0]; 1261 Integer discriminator = (Integer)row[1]; 1262 Integer childId = (Integer)row[2]; 1263 1264 // The second time we see the same child, the parent_id column should be nullified 1265 if (childId.equals(lastChildId)) 1266 { 1267 parentId = null; 1268 } 1269 lastChildId = childId; 1270 Item parentType = Item.EXTRACT; 1271 if (discriminator == 1) 1272 { 1273 parentType = Item.BIOSOURCE; 1274 } 1275 else if (discriminator == 2) 1276 { 1277 parentType = Item.SAMPLE; 1278 } 1279 1280 setQuery.setParameter("parentId", parentId, Type.INT.getTypeWrapper().getHibernateType()); 1281 setQuery.setInteger("parentType", parentType.getValue()); 1282 setQuery.setInteger("childId", childId); 1283 setQuery.executeUpdate(); 1284 } 808 1285 809 1286 HibernateUtil.commit(tx); -
trunk/src/install/net/sf/basedb/install/InitDB.java
r5703 r5730 74 74 if (update) 75 75 { 76 if (schemaVersion < 100)76 if (schemaVersion < 99) 77 77 { 78 78 Update.updateToBase3(progress, rootLogin, rootPassword);
Note: See TracChangeset
for help on using the changeset viewer.