--------------------------------------------------------------------------------------------------------------- 18-AUG-2006 The table CRUOGEO_MEAS_ENV_LOG was created to store the records updated or deleted from the table CRYOGEO_MEAS_ENV. The AFTER UPDATE OR DELETE trigger CRYOGEO_MEAS_ENV_AUD was created to copy the previous state of a record after it is updated or deleted. --------------------------------------------------------------------------------------------------------------- 22-AUG-2006 The table ACCESS_LOG created to record all non DBA logins on the MMSMA account. The logons are registered by the AFTER LOGON trigger AFTER_LOGON_LOG. This trigger also controls the direct access to the MMSMA account (access i limited, allowed users are: gil, gaozw, wildner, natalia) --------------------------------------------------------------------------------------------------------------- 24-AUG-2006 The trigger BEFORE LOGOFF was added to register the time when the database session in MMSMA was finished. --------------------------------------------------------------------------------------------------------------- 29-AUG-2006 The AFTER_LOGON_LOG was disabled. --------------------------------------------------------------------------------------------------------------- 04-SEP-2006 The AFTER_LOGON_LOG was enabled. --------------------------------------------------------------------------------------------------------------- 06-SEP-2006 The view INSTALL_PACK_SHIFT was recreated to reference the existing SSS tables. --------------------------------------------------------------------------------------------------------------- 15-SEP-2006 It was needed to invalidate the cold SSW and CHA measurements for SSS639. There was only one measurement for the corresponding steps, and the database integrity constraints on the tables SSSGEO2_MAG_STEP_CAT and SSSGEO2_MEAS_ENV did not allow to have a unique measurement for a step loaded as invalid. To enable the storage of invalid measurement the NOT NULL constraints on the valid measurement ID had to be disabled. The statements were: alter table SSSGEO2_MAG_STEP_CAT disable constraint SSSGEO2_MAGSTEP_VALIDMEASID_NN; alter table SSSGEO2_MAG_STEP_CAT disable constraint SSSGEO2_MAGSTEP_VALIDATEDBY_NN; The package SSSGEO2_ADMIN_UTL was created to keep the procedures to validate and invalidate the SSS measurements. To log the changes in the tables SSSGEO2_FINAL_MEAS_CAT and SSSGEO2_MAG_STEP_CAT the corresponding LOG tables were created, and AFTER UPDATE OR DELETE triggers added to perform the automatic logging. --------------------------------------------------------------------------------------------------------------- 18-SEP-2006 'zinur' is added to the list of MMSMA users with administration access. The check constraints on the MAGNET_TYPE column for the SSSGEO2 tables were found to be invalid. There was a misprint in the MQTLH magnet type. The problem appeared when the WP18 data for the SSS680 was tried to be uploaded. Solution: 1. Create 2 reference tables for the list of allowed magnet types for WP15 and WP18: REF_SSS_MAGNET_TYPE - for WP15 REF_SSS_MAGNET_TYPE_PLUS - for WP18 2. Disable the check constraints on the MAGNET_TYPE column for the tables: SSSGEO2_ANGLE_SSW SSSGEO2_AXIS SSSGEO2_AXIS_SSW SSSGEO2_CALC_ANGLE_SSW SSSGEO2_CALC_APERTURE SSSGEO2_CALC_AXIS SSSGEO2_CALC_AXIS_SSW SSSGEO2_MEAS_APERTURE 3. Add referntial integrity constraints on the column MAGNET_TYPE in these tables: MAGNET_TYPE must be registered in the table REF_SSS_MAGNET_TYPE for the tables: SSSGEO2_ANGLE_SSW SSSGEO2_AXIS_SSW SSSGEO2_CALC_ANGLE_SSW SSSGEO2_CALC_APERTURE SSSGEO2_CALC_AXIS SSSGEO2_CALC_AXIS_SSW MAGNET_TYPE must be registered in the table REF_SSS_MAGNET_TYPE_PLUS for the tables: SSSGEO2_AXIS SSSGEO2_MEAS_APERTURE --------------------------------------------------------------------------------------------------------------- 19-SEP-2006 Tables CCGEO (as defined by Jerome) created in MMSMA account. Upload of 9 cc successful. SELECT privileges given to MAS_ALL and MAS_PDA. Table SSSGEO2_AXIS_LOG created. The AFTER UPDATE or DELETE trigger on SSSGEO2_AXIS was created to log changes in SSSGEO2_AXIS. The view FID_SHIFT extended to contain the shift data for the connection cryostats. The view FID_SHIFT_EXTENDED was created to select all types of magnets and cryostats with shifts data (null if not yet inserted). --------------------------------------------------------------------------------------------------------------- 20-SEP-2006 The CONNCRYOGEO views was created in MMSMA account. SELECT privilege was granted to all CCGEO tables and CONNCRYOGEO views to SURVEY with grant option. --------------------------------------------------------------------------------------------------------------- 21-SEP-2006 The view CONNCRYOGEO_D_POINTS was created on request of Patrick Bestmann. --------------------------------------------------------------------------------------------------------------- 22-SEP-2006 LOG tables and triggers created for all DIPSEPGEO tables. The package DIPSEPGEO_UTL was created to define the procedures for DIPSEPGEO measurement deletion and restoring. --------------------------------------------------------------------------------------------------------------- 24-SEP-2006 1. The view FID_SHIFT_EXTENDED corrected to display only the measurements with GA_ON. 2. The 3 new columns were added to each of the alignment tables: CCGEO_ALIGNMENT, DIPSEPGEO_ALIGNMENT, LOWBETAGEO_ALIGNMENT, SSSGEO2_ALIGNMENT align_date date, -- the date when shifts were assigned (specified in the source file) insert_user varchar2(30), -- the username of the user, which has loaded the data insert_login varchar2(30) -- the Nice login of the user, which has loaded the data The BEFORE INSERT triggers were added to thses tables to fill automatically the columns LOADING_DATE, INSERT_USER, INSERT_LOGIN 3. The package SHIFTS_UPLOAD was created to hold the procedures to upload shifts for all types of equipment (not yet for the dipoles) and to delete the uploaded shift for a magnet. --------------------------------------------------------------------------------------------------------------- 25-SEP-2006 1. Package SHIFTS_UPLOAD finished. The procedure SHIFTS_UPLOAD_DO for the upload created. 2. New user ATMAS_SHIFTS_UPLOAD is created to connect to the database from the shifts upload macro. EXECUTE on the procedure SHIFTS_UPLOAD_DO granted to this user from MMSMA. 3. The table CRYOGEO_ALIGMENT extended in the same way as all other alignment tables (columns ALIGN_DATE, INSERT_USER, INSERT_LOGIN are added, and the BEFORE INSERT trigger created). 4. The table CRYOGEO_ALIGNMENT had a problem with duplicated final alignment. The change of the UNIQUE KEY fixes it: alter table CRYOGEO_ALIGNMENT disable constraint CRYOGEO_ALIGNMENT_UKEY; alter table CRYOGEO_ALIGNMENT add ( constraint CRYOGEO_ALIGNMENT_UKEY2 unique (CHOICE_ID, FINAL_ALIGN) using index tablespace INDX); --------------------------------------------------------------------------------------------------------------- 28-SEP-2006 The view FID_SHIFT_EXTENDED is modified to display non GA_ON measurements for the separator dipoles. The package SHIFTS_UPLOAD was modified to accept magnet numbers along with magnet names and to assign the shift to the unique measurement for the separator dipoles when there is no measurement with GA_ON. --------------------------------------------------------------------------------------------------------------- 29-SEP-2006 The table SHIFT_FILES and the sequence SHIFT_FILES_ID were created to store the shift source files. The column FILE_ID was added to the table DIPSEPGEO_ALIGNMENT to store the reference to the file in SHIFT_FILES. Package SHIFTS_UPLOAD and procedure SHIFTS_UPLOAD_DO were modified to load in the database the source file info and the file itself. Package and other alignment tables are to be modified (FILE_ID added). --------------------------------------------------------------------------------------------------------------- 02-OCT-2006 The column FILE_ID is now added to all ALIGNMENT tables (CCGEO, CRYOGEO, DIPSEPGEO, LOWBETAGEO and SSSGEO2). This column declared NOT NULL NOVALIDATE (existing records can stay without the reference to the file). The package SHIFT_FILES is finalized (stores the file content for all types of equipment). The following packages were recovered from the database data dictionary and the smtp server changed from smtp.cern.ch to cernmx.cern.ch: CHECK_MAG_NAME MAG_GEO_IN_MTF For all alignment tables except CRYOGEO the BEFORE INSERT triggers were created to check the uniqueness of the upload of a given shift values (shift values, sender, comment and date). --------------------------------------------------------------------------------------------------------------- 03-OCT-2006 The table SSSGEO2_REF_CSSW_OFFSETS created. This table stores the offset values to be applied to all points of the SSS cold SSW measurements for different groups of SSS. SELECT privileges were granted to MAS_PDA and READ_ALLMAS. The view SSSGEO2_CALC_AXIS_SSW_OFFSET was created to be used in the SSS Geometry viewer with the option when cold SSW offsets are applied. The view gives all valid WP15 calculations with appropriate offsets applied. --------------------------------------------------------------------------------------------------------------- 04-OCT-2006 The views FID_SHIFT_FILES and FID_SHIFT_FILE_SEARCH created for the web-site Shifts files archive. --------------------------------------------------------------------------------------------------------------- 05-OCT-2006 The view FID_SHIFT_EXTENDED changed to show the MOLE used in SSS and SPSSS measurements. The views SSSGEO2_V_ANGLE_AVG_STAT, SSSGEO2_V_ANGLE_AVG_1 and SSSGEO2_V_ANGLE_AVG_2 were created on request of Marco Buzio (to make the stats on the field angle). --------------------------------------------------------------------------------------------------------------- 06-OCT-2006 To enable the manual attribution of the source file ID to the shift alignment records the NOT NULL constraints on the FILE_ID in the ALIGNMENT tables were temporarily disabled. The BEFORE INSERT triggers ensure that all new records will have the FILE_ID instead. The view FID_SHIFT_RECORDS was created to make the updates of FILE_ID through the web interface easy. The INSTEAD OF trigger FID_SHIFT_RECORDS_U enables updates on this view. --------------------------------------------------------------------------------------------------------------- 18-OCT-2006 The package SHIFTS_UPLOAD was modified to reject the SSS and SPSSS shifts if the Y shift is other than 0. --------------------------------------------------------------------------------------------------------------- 19-OCT-2006 The tables SSS_QUENCH and SSS_QUENCHE_DATA were created to store temppararily the SSS quench results from SM18. SELECT granted to MAS_ALL and MAS_PDA. --------------------------------------------------------------------------------------------------------------- 23-OCT-2006 The new view IDCARD_VIEW_LAST_WP08 with the same data as in the IDCARD_VIEW, but for the last step at WP08 was created. SELECT grants given to MTFLINK with grant option ASBMGR with grant option MAS_ALL MAS_PDA The table SSS_QUENCH_DATABASE to store the quench results from the Access database sent by Georges-Henry Hemelsoet. 19949 records inserted. --------------------------------------------------------------------------------------------------------------- 25-OCT-2006 The table SSSGEO2_END_COVERS_LOF was created to store updated abd deleted records from the table SSSGEO2_END_COVERS. Corresponding triggers on both tables were also created. --------------------------------------------------------------------------------------------------------------- 26-OCT-2006 The triggers CCGEO_MEASMAGSTEPTEMP_BI on the table CCGEO_MEAS_MAGSTEP_TEMP was modified to treat the MAGNET_NAME of 20 characters (to enable the last 2 files upload). --------------------------------------------------------------------------------------------------------------- 27-OCT-2006 The package MAG_GEO_IN_MTF was modified to send the mails to Natalia as well. All database links re-created. Job running the package MAG_GEO_IN_MTF.main killed (it hangs since 10-OCT). --------------------------------------------------------------------------------------------------------------- 30-OCT-2006 IDCARD_VIEW_LAST_WP08 view modified to display onlu semifidu = 'no' measurements. --------------------------------------------------------------------------------------------------------------- 31-OCT-2006 The new package SHIFTS_ADMIN to make manual interventions on the shifts and i-packs was created. All actions on the shifts made with this package will be registered in the table SHIFT_MANUAL_CHANGES. The table and the package are visible from MAS_ALL, but the package can be run only by registered Nice users (wildner and natalia). --------------------------------------------------------------------------------------------------------------- 03-NOV-2006 A new procedure SHIFT_UPLOAD_END was created to delete the just uploaded shifts source file if none of the shifts records is inserted (to be used in the upload macro). The EXECUTE grants is given to ATMAS_SHIFTS_UPLOAD. The table SHIFT_MANUAL_CHANGES has new column COMMENTS, and the package SHIFTS_ADMIN is changed accordingly. --------------------------------------------------------------------------------------------------------------- 08-NOV-2006 The tables MV_SSS_IDCARD_MAG_WARM and MV_SPESSS_IDCARD_MAG_WARM were created to speed the access to the data from the views SSS_IDCARD_MAG_WARM and SPESSS_IDCARD_MAG_WARM. The procedure P_SSS_IDCARD_REFRESH is used to update these tables. --------------------------------------------------------------------------------------------------------------- 09-NOV-2006 There was a problem with the treatment of the renamed magnets for the WMF measurements. The new trigger WMF2_MAG_CAT_BI_RENAMED_TO was created to automatically assign the RENAMED_TO fiels before the upload. --------------------------------------------------------------------------------------------------------------- 15-NOV-2006 The view FID_SHIFT_EXTENDED was modfified to show the AS or GEO mole for the low beta triplets. --------------------------------------------------------------------------------------------------------------- 18-NOV-2006 The view FID_SHIFT and the views for the Web-interface were modfified to show the AS or GEO mole for the SSS and low beta triplets. --------------------------------------------------------------------------------------------------------------- 29-NOV-2006 The table SHIFT_FILES was extended with 3 columns LINKS_OK, LINKS_OK_BY and LINKS_OK_ON. These column are used to store the Y indicator for the files, for which all links to the magnet shifts are restored. The queries in the derived data package was changed to take for the classification, racetrack and twist the averaged values of X and Z at each Y coordinate. Like that we avoid the crash of the derived data package on filling the tables RACETRACK and TWIST. The upload of the coil size data (procedure MAS_D_CS.LOAD_COIL_SIZE_TABLES.FILL_MAIN) was changed for not to take the coils with more than one record for the approved layer, and with the too long coil name. The backlog due to the crash because of too long coil name was worked and all records copied. The one invalid coil name was corrected. --------------------------------------------------------------------------------------------------------------- 04-DEC-2006 The constraint on the room temperature on the table CRYOGEO_MEAS_ENV was extended to accept 311K specified in the fidu of 1372. The upper limit was put the same as for all other magnet types - 330K. alter table CRYOGEO_MEAS_ENV drop constraint GEO_ENV_ROOMTEMP_CK; alter table CRYOGEO_MEAS_ENV add ( constraint GEO_ENV_ROOMTEMP_CK check (Room_Temperature between 275 and 330) ); --------------------------------------------------------------------------------------------------------------- 11-DEC-2006 The triggers MagName_GEO_ENV and MAGNAME_GEO_AXIS were modified to accept the dipoles without diode (category HCMBB_A001). Tne condition when checking whether the dipole is in MTF is changed to be: FROM MTF_CATALOG_COLD_MASS WHERE substr(part_id,13)=substr(:new.MAGNET_NU,1,1)||'000'||substr(:new.MAGNET_NU,2) AND (PARENT IS NOT NULL or CATEGORY = 'HCMBB_A001') --------------------------------------------------------------------------------------------------------------- 12-DEC-2006 An auxiliary table X_DIPOLES_WITHOUT_DIODE was created, and the list of all cold masses without diode was inserted in it (data provided by Jurgen Heer [cern@as-g.it]). The cross check with the MTF list of cold masses shew that another category should be added to the check upon CRYOGEO measurements upload, and the triggers MagName_GEO_ENV and MagName_GEO_AXIS were changed accordingly. --------------------------------------------------------------------------------------------------------------- 16-DEC-2006 The PL/SQL packages, which send the error notification mail to natalia were changed to send the mail to Elena Wildner: MMSMA.Check_Mag_Name MMSMA.Mag_Geo_in_MTF MMSMA.SHIFT_UPLOAD MAS_D_CS.LOAD_COIL_SIZE_TABLES Also, the list of the allowed OS users in the shift invalidation procedure in the package MMSMA.SHIFTS_ADMIN is now limited to Elena Wildner. All modified files are copied into the repository: \\webh11\lhc-div-mms\tests\Geometric_data\database