package CSR; # # Giovanni Bortoluzzi IGM - CNR Bologna # Package perl per elaborazioni database SAMPLES # require 5.000; require Exporter; @ISA = qw(Exporter); @EXPORT = qw ( %CSR %SAMPLER_NAME @NAMES %MONTH_BY_NUM %MONTH_BY_NAME %MONTH_BY_NAME_N print_data_string make_dict create_sample_dict populate_sample_dict create_sample_stations ); @EXPORT_OK = qw(); use vars qw($VERSION); %SAMPLER_NAME = ( GC => "GRAVITY CORER", GT => "GRAVITY CORER TRIGGER", SW => "SEDIMENT/WATER", "PI" => "PISTON CORER", GRS => "GRAB SMALL", "GRL" => "GRAB LARGE", "BCS" => "BOX CORER SMALL", BCL => "BOX CORER LARGE" ); @NAMES = qw ( CRUISE_ID ALT_CRUISE_ID LEG URL_HTML URL_PDF START_DATE END_DATE VESSEL_NAME CHIEF_EXPEDITION CE_ORG PROJECT FUNDING COLLATE_CENTER REVISION_DATE PLATFORM PORT_DEPARTURE PORT_RETURN WEST_LON SOUTH_LAT EAST_LON NORTH_LAT GENERAL_LOCATION SUB_LOCATION AVAILABILITY CHIEF_SCIENTIST CS_ORG PARAMETER_LIST INSTRUMENT_LIST TRACK_CHART_NAME COORDINATING_BODY PURPOSE ); %MONTH_BY_NUM = ('1','Jan','2','Feb', '3','Mar','4','Apr','5','May', '6','Jun', '7','Jul', '8','Aug', '9','Sep','10','Oct','11','Nov','12','Dec', ); %MONTH_BY_NAME = ('Jan','1','Feb','2','Mar','3','Apr','4','May','5','Jun','6', 'Jul','7','Aug','8','Sep','9','Oct','10','Nov','11','Dec','12', ); %MONTH_BY_NAME_N = ('JAN','1','FEB','2','MAR','3','APR','4','MAY','5','JUN','6', 'JUL','7','AUG','8','SEP','9','OCT','10','NOV','11','DEC','12', ); sub print_data_string { my ($type)= @_; my $N,$n; $N = undef; for $n (@NAMES) { $n =~ s/\s+//g; if ($type eq "vars") { ${$n} =~ s/\?//; $N .= "${$n}, "; } elsif ($type eq "names") { ${$n} = ""; $N .= "$n, " } else { die "usage : print_data_string vars|names ..."; } } $N =~ s/,\s+$//; print "$N\n"; } sub make_dict { my ($table) =@_; my ($key); for $key (sort {$a<=>$b} keys %{$table}) { print "$key => ${$table}{$key}\n"; } } sub create_sample_dict { print <$b} keys %{$table}) { print "insert into SAMPLE_DICT (DICT,KEYWORD,VALUE) values "; $K = $key =~ /\d+/ ? $key : "\'".${key}."\'" ; print "(\'$table\', $K, \'${$table}{$key}\');\n"; } # print ")\n\n"; } %CSR = ( DICTIONARY => { 0 => "DICT, 80, t,,,, varchar, Dict.Category", 1 => "KEYWORD, 1, si,,,, smallint, keyword", 2 => "VALUE, 255, t,,,, varchar, value", }, CRUISES => { -1 => "CRUISE_ID NOT NULL, 24, c,,,, Char.Code,Identificativo Campagna", 0 => "ALT_CRUISE_ID , 24, c,,,, Char.Code, Alt. Identificativo Campagna", 1 => "LEG, 4, c,,,, Char.Code,Identificativo Leg", 3 => "START_DATE, 1, d,,,, yyyy-mm-dd,Data Inizio", 4 => "END_DATE, 1, d,,,, yyy-mm-dd,Data Fine", 5 => "VESSEL_NAME, 48, t,,,, Char.Code,Nave", 10 => "CHIEF_EXPEDITION, 255, t,,,, Char.Code,Capo Spedizione", 11 => "CE_ORG, 255, c,,,, Char.Code, Organization", 12 => "PROJECT, 255, t,,,, String, Project", 13 => "FUNDING, 255, t,,,, String, Funding Organization", 20 => "COLLATE_CENTER, 48, t,,,, String, Collate Center", 21 => "REVISION_DATE, 1, d,,,, yyyy-mm-dd, Revision date", 23 => "PLATFORM, 48, t,,,, Char.Code, Platform", 30 => "PORT_DEPARTURE, 48, t,,,, Char.Code,Capo Spedizione", 31 => "PORT_RETURN, 48, t,,,, Char.Code,Capo Spedizione", 44 => "WEST_LON, , r,,,, float, ", 45 => "SOUTH_LAT, , r,,,, float, ", 46 => "EAST_LON, , r,,,, float, ", 47 => "NORTH_LAT, , r,,,, float, ", 50 => "GENERAL_LOCATION, 255, t,,,, Char.Code,Zona di Lavoro", 51 => "SUB_LOCATION, 255, t,,,, Char.Code,Zona di Lavoro", 60 => "AVAILABILITY, 24, t,,,, char, Availability", 70 => "CHIEF_SCIENTIST, 255, t,,,, Char.Code,Chief Scientist", 72 => "CS_ORG, 12, t,,,, Char.Code, CS Organization", 73 => "COORDINATING_BODY, 255, t,,,, Char.Code, CS Organization", 74 => "PARTICIPATING_BODY, 255, t,,,, Char.Code, CS Organization", 80 => "PARAMETER_LIST, 255, t,,,, String, Track Chart Name", 81 => "INSTRUMENT_LIST, 255, t,,,, String, Track Chart Name", 90 => "TRACK_CHART_NAME, 24, t,,,, String, Track Chart Name", 100 => "PURPOSE, 1024, t,,,, Char.Code,Commento", 110 => "URL_HTML, 255, t,,,, String, Coordinating Body", 111 => "URL_PDF, 255, t,,,, String, Coordinating Body", 112 => "URL_MAP, 255, t,,,, String, Coordinating Body", }, STATIONS => { -1 => "SAMPLE_ID NOT NULL, 12, c,,,, char, Sample ID", 0 => "CRUISE_ID NOT NULL, 12, c,,,, char, Cruise ID", 1 => "GEO_LOC, 24, t,,,, varchar, geographical location", 2 => "MEASURE_TYPE, 1, si,,,, smallint, Point,Line,Area", 3 => "REFERENCE_ID, 12, c,,,, char, Alternate ID", 4 => "OBJECTIVE, 255, t,,,, varchar, Sample objective", 5 => "PHYSIOGRAPHIC_PROVINCE, 1, si,,,, smallint, ", 6 => "MONITORING_SITE, 255, t,,,, varchar, Description of the monitoring site", 7 => "COMMENTS, 255, t,,,, varchar, Any further comment on the sample/core", 8 => "CRUISE_REPORT, 255, t,,,, ", 9 => "REFERENCE, 255, t,,,, Title(s) of reference(s)/publication(s)/report(s)", 10 => "START_DATE, , d,,,, date, ", 11 => "START_TIME, , ti,,,, time, ", 12 => "END_DATE, , d,,,, date, ", 13 => "END_TIME, , ti,,,, time, ", 20 => "DATUM, 6, t,,,, varchar, WGS84,ED50 ...", 21 => "NAVIGATION_SYSTEM, 1, si,,,, smallint, 2=GPS,3=DGPS ", 22 => "GRID_TYPE, 1, si,,,, smallint, 1=UTM,2-GEO ", 23 => "CENTRAL_MERIDIAN, , f,,,, float, ", 24 => "START_LON, , r,,,, float, ", 25 => "START_LAT, , r,,,, float, ", 26 => "END_LON, , r,,,, float, ", 27 => "END_LAT, , r,,,, float, ", 28 => "START_HDG, , r,,,, float, ", 29 => "END_HDG, , r,,,, float, ", 30 => "START_WATER_DEPTH, , f,,,, float, Water depth", 31 => "END_WATER_DEPTH, , f,,,, float, Water depth", 32 => "DEPTH_CORRECTION, 1, c,,,, char, enum (Corrected, Not-corrected,Unknown), ", 33 => "REFERENCE_LEVEL, 80, t,,,, varchar, ", 39 => "SAMPLING_DEVICE_NAME, 24, t,,,, varchar, ", 40 => "SAMPLING_DEVICE, 1, si,,,, smallint, ", 41 => "SAMPLING_DEVICE_MOUNT, 24, t,,,, varchar, sections, pipes etc", 42 => "LENGTH_CORING_DEVICE, , f,,,, float, ", 43 => "SAMPLE_DIAMETER, , f,,,, float, ", 44 => "SAMPLING_PENETRATION, , f,,,, float, ", 45 => "SAMPLE_LENGTH, , f,,,, float, ", 46 => "SEDIMENT_TYPE, 255, t,,,, varchar, #Summary of sediment type(s) in sample/core", 47 => "SAMPLE_RECOVERY, 255, t,,,, varchar(255), #Indication of the sample recovery", 48 => "SURFACE_SAMPLE, 1, c,,,, enum (Y,N), ", 49 => "SUB_SURFACE_SAMPLE, 1, c,,,, enum (Y,N), ", 60 => "DATA_SOURCE_HOLDER, 12, t,,,, varchar, ", 61 => "SAMPLE_STATE, 1, si,,,, smallint, ", 62 => "SAMPLE_STORAGE_CONDITION, 1, si,,,, smallint, ", 63 => "TREATMENT, 24, t,,,, varchar, #Which treatments have been performed on the samples/cores. The user can enter up to 5 different treatments per location.", 64 => "MEASURED_PARAMETERS, 24, t,,,, varchar, #Parameters derived from tests on the samples/cores. The user can enter up to 5 different parameters per location.", 65 => "BASAL_AGE, 80, t,,,, varchar, Age of the bottom of the core. ", }, UUOO => { 0 => "UO, 6, c,,, Char.Code, Data Reference ID", 1 => "DATASET, 6, c,,, Char.Code, Dataset Reference ID", 8 => "DATA_FROM, 255, t,,, String, Data Reference", }, INSTRUMENTS => { 0 => "CRUISE_ID NOT NULL, 12, c,,, Char.Code, CRUISE ID", 2 => "TYPE, 6, c,,, Char.Code, Operation", 3 => "INSTR_ID, 6, c,,, Char.Code, Instrument Code", 4 => "INSTR, 255, t,,, String, Instrument Description", }, ); %CSR_TAB = ( 0 => "UUOO, Data References and Cruises", 1 => "CRUISES, Cruises", 2 => "STATIONS, Navigation Lines", 8 => "SEDIMENTOLOGICAL_DESCRIPTION, Sub Sample Description", 9 => "DICT, dictionaries", ); # DICTIONARIES ... %PHYSIOGRAPHIC_PROVINCE = ( 2 => "Continental shelf" , 11 => "Abyssal hills" , 10 => "Abyssal plain" , 9 => "Archipelagic apron" , 13 => "Aseismic oceanic rise or ridge" , 16 => "Axial valley" , 33 => "Beach" , 35 => "Coastal lagoon" , 6 => "Continental rise" , 4 => "Continental slope" , 24 => "Delta or cone" , 34 => "Estuary" , 19 => "Fracture zone" , 23 => "Harbor, shallow bay" , 22 => "Inland fresh water lake" , 29 => "Inner continental shelf" , 5 => "Insular rise" , 1 => "Insular shelf" , 3 => "Insular slope" , 32 => "Lower continental slope" , 7 => "Marginal plateau or borderland, deeper than 100 fms" , 20 => "Marginal sea" , 26 => "Mid-ocean canyon or channel" , 27 => "Oceanic plateau" , 36 => "Other" , 30 => "Outer continental shelf" , 14 => "Ridge crest" , 15 => "Ridge flank" , 28 => "Saltwater inland lake" , 12 => "Seamount or seamount province" , 21 => "Small ocean basin" , 25 => "Submarine canyon" , 18 => "Trench, continental margin" , 17 => "Trench, insular" , 8 => "Unspecified" , 31 => "Upper continental" , ); %SAMPLING_DEVICES = ( 48 => "Grab (Unknown Type)" , 19 => "Addie-Ewing-Taaf Corer" , 5 => "Air vibro corer" , 28 => "Airlift counterflush corer" , 41 => "Amdrill Corer" , 55 => "Amon Grab" , 69 => "Berthois Cone" , 65 => "Boillot Dredge" , 7 => "Box corer" , 35 => "Camera mounted corer" , 66 => "Charcot Dredge" , 93 => "Cone Penetration Test Device (CPT)" , 80 => "Core Drilling (Unknown type)" , 12 => "Corer (unknown nature)" , 11 => "Corer (unknown type)" , 37 => "Dart corer" , 85 => "Diver hand taken sampler" , 83 => "Dragueta AC underway sampler" , 62 => "Dredge (Unknown Nature)" , 61 => "Dredge (Unknown Type)" , 78 => "Drilling (Unknown nature)" , 77 => "Drilling (Unknown type)" , 53 => "Egg Grab" , 60 => "Ekman Grab" , 1 => "Electrical vibro corer" , 23 => "Electro Corer with Rotation Table" , 68 => "Endoume Dredge" , 67 => "Epibenthic type Sanders et Hessler Dredge" , 81 => "Flexo Drilling" , 57 => "Free Fall Sampler" , 34 => "Free-fall corer" , 30 => "Gemini corer" , 92 => "General Geotechnical Device (GTD)" , 6 => "Geodoff mk2" , 49 => "Grab (Unknown Nature)" , 33 => "Gravity corer" , 10 => "Hammer corer" , 46 => "Hamon Grab" , 36 => "Hand-held corer" , 45 => "Ice Wedge Corer" , 25 => "IGBA Corer" , 44 => "Kajak Corer" , 39 => "Kastenlot corer" , 21 => "Kogler Corer" , 14 => "Kulleberg Corer + Heat Flow" , 26 => "Langmuir Corer (Glass, Fragments)" , 88 => "Lead and Line Sampler" , 43 => "Limnos Corer" , 4 => "Liner vibro corer" , 71 => "Lister Dredge" , 40 => "Long Piston corer" , 87 => "Low Tide Manual Sampling" , 51 => "Lucas Sampler Grab" , 82 => "Manual Drilling" , 17 => "Mecabolier Corer" , 42 => "Mounted Barge Corer" , 38 => "Multi corer" , 29 => "Niemist� corer" , 70 => "Nodules Dredge" , 79 => "Oil Drilling" , 59 => "Okean Grab" , 90 => "Orange Peel Bucket" , 76 => "OSCOR drilling device" , 94 => "Other Sampling Device" , 50 => "Petersen Grab" , 18 => "Pilot Corer" , 32 => "Piston corer" , 13 => "Piston Corer Kullenberg" , 24 => "Push-Sampler Corer (TMG) Geotechnical Module" , 64 => "Rallier Dredge" , 8 => "Reinck box corer" , 73 => "Rock dredge" , 75 => "Rock drill" , 54 => "Romanowswki Grab" , 86 => "Sampling by Diver" , 9 => "Scripps box corer" , 74 => "Sediment drill" , 91 => "Sediment Trap" , 56 => "Shipeck Grab" , 20 => "Sipan Corer (Nodules)" , 58 => "Smith MC Intyre Grab" , 63 => "Soft Sediment Dredge (Unknown Nature)" , 16 => "Stetson-Hill Rock Corer" , 27 => "Straight flush wireline" , 84 => "Submersible (undersea sampling)" , 31 => "Trigger weight corer" , 3 => "Trilflip vibro corer" , 52 => "TV Grab" , 95 => "Unknown" , 22 => "Usnel Corer" , 47 => "Van Veen Grab" , 2 => "Vibro corer" , 89 => "Vibro Hammer" , 15 => "Ville Rock Corer" , 72 => "Warren Dredge" , 200 => "IGM SW109", ); %TREATMENT = ( 1 => "Lithological log" , 6 => "Particle size analysis" , 10 => "Textural classification" , 7 => "Geochemical analysis" , 8 => "Mineralogical analysis" , 9 => "Organic analysis" , 11 => "Other" , 4 => "Photo" , 3 => "Sample description" , 2 => "Soil profile/borehole description" , 5 => "X-ray radiograph" , 20 => "Multi Sensor Core Logger", ); %MEASURED_PARAMETERS = ( 15 => "Other" , 1 => "Attenberg limits" , 4 => "Carbonate content" , 13 => "Compression" , 8 => "Conductivity" , 3 => "Density" , 11 => "Magn. susc." , 5 => "Organic matter content" , 10 => "P-wave velocity" , 14 => "Paleomagnetic parameters" , 12 => "Permeability" , 2 => "Shear strength" , 9 => "Temperature" , 7 => "Unit weight" , 6 => "Water content" , ); %DATA_SOURCE_HOLDER = ( "NITG" => "Netherlands Institute of Applied Geoscience-TNO" , "AWI" => "Alfred Wegener Institute" , "AWZ-WWK" => "AWZ-WWK" , "BGS" => "British Geological Survey" , "BSH/MUDAB" => "Bundesamt f�r Seeschiffahrt und Hydrographie" , "NLfB" => "Bundesanstalt Geow. und Rohst." , "BRGM/Brest" => "Bureau de Recherche G�ologique et Mini�res" , "GEUS" => "Denmark Greenland Geological Survey" , "Geoteam" => "Fugro-Geoteam AS" , "Geolab" => "Geolab Nor A/S" , "GSB" => "Geological Survey of Belgium" , "GSF" => "Geological Survey of Finland" , "GSI" => "Geological Survey of Ireland" , "NGU" => "Geological Survey of Norway" , "SGU" => "Geological Survey of Sweden" , "GEOMAR" => "GEOMAR" , "UB" => "GRC Geociencas Marinas" , "hydnavy" => "Hydrographic Service Greek Navy" , "IGME" => "Institute of Geology and Mineral Exploration" , "IMBCCRETA" => "Institute of Marine Biology" , "ICM-CSIC" => "Instituto de Ciencias del Mar" , "IGMCNR" => "Instituto di Geologia Marina" , "IEO" => "Instituto Espa�ol de Oceanograf�a" , "IGM-DGM" => "Instituto Geologico e Mineiro" , "ITGE" => "Instituto Technologico Geominero de Espana" , "MARIS" => "MARIS BV" , "mma" => "Ministerio Medio Ambiente" , "Noteby" => "Multiconsult - NOTEBY" , "NCMR" => "National Centre of Marine Research" , "Saclantcen" => "NATO Saclant Undersea Research Centre" , "NGI" => "Norwegian geotechnical institute" , "NPD" => "Norwegian Petroleum Directorate" , "NPI" => "Norwegian Polar Institute" , "Kummeneje" => "Scandiaconsult AS" , "SGN" => "Servizio Geologico Nazionale" , "IKU" => "Sintef Petroleum Research" , "SOC" => "Southampton Oceanography Centre" , "UNIVENEZIA" => "Universita Ca Foscari" , "UNINAPOLI" => "Universita di Napoli" , "UNIROMA" => "Universita di Roma" , "UIB" => "University of Bergen" , "patras" => "University of Patras" , "UIT" => "University of Troms�" , "ITU" => "Istanbul Technical University", ); %DATUM = ( 1 => "WGS84" , 2 => "ED50" , 3 => "GAUSS-BOAGA" , ); %SAMPLE_STORAGE_CONDITION = ( 11 => "4 degree cold store" , 1 => "Dry" , 7 => "Frozen" , 13 => "Other" , 4 => "Partial core" , 8 => "Refrigerated" , 10 => "Room temperature, dry" , 9 => "Room temperature, moisture sealed" , 6 => "Stored in D-tubes" , 5 => "Sub-sampled" , 3 => "Text information only" , 12 => "Unknown" , 2 => "Used-up, no core remaining" , ); %PROJECT = ( 1 => "EUMARSIN" , 2 => "EUROCORE" , ); %GRID_TYPE = ( 1 => "UTM" , 2 => "GEO" , ); %SAMPLE_STATE = ( 3 => "Bagged subsamples only" , 4 => "Other" , 1 => "Sample exists" , 2 => "Used up, no sample remaining" , ); %NAVIGATION_SYSTEM = ( 6 => "BEARINGS" , 1 => "DECCA" , 3 => "DGPS" , 2 => "GPS" , 5 => "LORAN" , 8 => "OMEGA" , 10 => "Other" , 7 => "RADAR" , 4 => "Transit satellite" , ); %MEASURE_TYPE = ( 1 => "Point" , 3 => "Area" , 2 => "Track" , ); %SAMPLES_TYPE = ( GL => "Volcanic Glass", BA => "Basalt", PD => "Peridotite", GA => "Gabbro", HYS => "Hydrothermal Sulfides", HYC => "Sediment Hydrothermal Iron/Manganese rich", CRU => "Crust Iron/Manganese rich", NOD => "Nodule Iron/Manganese rich", SED => "Sediment", ); %UUOO_TAB = ( IGM => "0, ", SIM => "1, ", SHU => "2, ", LAM_BRU => "3, ", UNI_PV => "4, ", UNI_FI => "5, ", UNI_PD => "6, ", UNI_PI => "7 ,", SOLMINE => "8, ", ); 1;