Index: /trunk/FACT++/src/spectrum.cc =================================================================== --- /trunk/FACT++/src/spectrum.cc (revision 19902) +++ /trunk/FACT++/src/spectrum.cc (revision 19903) @@ -147,5 +147,5 @@ ; - po::options_description queries("Queries"); + po::options_description queries("Analysis Query"); queries.add_options() ("analysis", var("analysis.sql"), "File with the analysis query. A default file is created automatically in the directory it does not exist.") @@ -172,5 +172,5 @@ ("print-queries", po_bool(), "Print all queries to the console. (For highlighting recompile with 'libsource-highlight-dev' installed)") #endif - ("mc-only", po_bool(), "Do not run an data realated queries (except observation times)") + ("mc-only", po_bool(), "Do not run a data related queries (except observation times)") ("verbose,v", var(1), "Verbosity (0: quiet, 1: default, 2: more, 3, ...)") ; @@ -342,5 +342,5 @@ } -void CreateBinning(Database &connection, ostream &qlog, const Binning &bins, const string &name) +void CreateBinning(Database &connection, ostream &qlog, const Binning &bins, const string &name, const string &comment) { mysqlpp::Query query0(&connection); @@ -348,9 +348,9 @@ "CREATE TEMPORARY TABLE Binning" << name << "\n" "(\n" - " bin SMALLINT UNSIGNED NOT NULL,\n" - " lo DOUBLE NOT NULL,\n" - " hi DOUBLE NOT NULL,\n" + " bin INT NOT NULL COMMENT 'Bin index (" << name << ")',\n" + " lo DOUBLE NOT NULL COMMENT 'Lower bin edge (" << name << ")',\n" + " hi DOUBLE NOT NULL COMMENT 'Upper bin edge (" << name << ")',\n" " PRIMARY KEY (bin) USING HASH\n" - ")"; + ") COMMENT='" << comment << "'"; qlog << query0 << ";\n" << endl; @@ -747,7 +747,7 @@ cout << separator("Binnings") << '\n'; - CreateBinning(connection, qlog, binning_theta, "Theta"); - CreateBinning(connection, qlog, binning_dense, "Energy_dense"); - CreateBinning(connection, qlog, binning_sparse, "Energy_sparse"); + CreateBinning(connection, qlog, binning_theta, "Theta", "Binning in zenith angle"); + CreateBinning(connection, qlog, binning_dense, "Energy_dense", "Dense binning in log10 Energy"); + CreateBinning(connection, qlog, binning_sparse, "Energy_sparse", "Sparse binning in log10 Energy"); Dump(flog, connection, "BinningTheta"); @@ -768,5 +768,5 @@ "CREATE TEMPORARY TABLE DataFiles\n" "(\n" - " FileId INT UNSIGNED NOT NULL,\n" +// " FileId INT UNSIGNED NOT NULL,\n" " PRIMARY KEY (FileId) USING HASH\n" ") ENGINE=MEMORY\n" @@ -815,13 +815,13 @@ Time start2; - /* 02:get-observation-time.sql */ + // For some reason, the comments do not appear in the "EXPLAIN CREATE TABLE" query mysqlpp::Query query2(&connection); query2 << "CREATE TEMPORARY TABLE ObservationTime\n" "(\n" - " `.theta` SMALLINT UNSIGNED NOT NULL,\n" - " OnTime DOUBLE NOT NULL,\n" + //" `.theta` INT COMMENT 'Zenith Angle bin index',\n" + " OnTime DOUBLE NOT NULL,\n"// COMMENT 'Effective on time in seconds per bin',\n" " PRIMARY KEY (`.theta`) USING HASH\n" - ") ENGINE=MEMORY\n" + ") ENGINE=MEMORY COMMENT='Effective on time of selected data files binning in zenith angle'\n" "AS\n" "(\n" @@ -871,7 +871,7 @@ "CREATE TEMPORARY TABLE MonteCarloFiles\n" "(\n" - " FileId INT UNSIGNED NOT NULL,\n" +// " FileId INT UNSIGNED NOT NULL,\n" " PRIMARY KEY (FileId) USING HASH\n" - ") ENGINE=MEMORY\n" + ") ENGINE=MEMORY COMMENT='Monte Carlo files selected by data Zenith Angle range'\n" "AS\n" "(\n" @@ -921,5 +921,5 @@ mysqlpp::Query query4(&connection); query4 << - "CREATE TEMPORARY TABLE MonteCarloArea ENGINE=MEMORY\n" + "CREATE TEMPORARY TABLE MonteCarloArea ENGINE=MEMORY COMMENT='Minimum and maximum impact radius of selected Monte Carlo files'" "AS\n" "(\n" @@ -975,17 +975,17 @@ "CREATE TEMPORARY TABLE Summary%100:table\n" "(\n" - " `.theta` SMALLINT UNSIGNED NOT NULL,\n" - " `.sparse_sim` SMALLINT UNSIGNED NOT NULL,\n" - " `.dense_sim` SMALLINT UNSIGNED NOT NULL,\n" - " CountN DOUBLE NOT NULL,\n" - " SumW DOUBLE NOT NULL,\n" - " SumW2 DOUBLE NOT NULL,\n" +// " `.theta` SMALLINT UNSIGNED NOT NULL COMMENT 'Zenith Angle bin index',\n" +// " `.sparse_sim` SMALLINT UNSIGNED NOT NULL COMMENT 'Energy bin index (sparse binning)',\n" +// " `.dense_sim` SMALLINT UNSIGNED NOT NULL COMMENT 'Energy bin index (dense binning)',\n" + " CountN INT UNSIGNED NOT NULL COMMENT 'Event count per bin',\n" + " SumW DOUBLE NOT NULL,\n"// COMMENT 'Sum of spectral weights',\n" + " SumW2 DOUBLE NOT NULL,\n"// COMMENT 'Sum of squared spectral weights',\n" " INDEX (`.theta`) USING HASH,\n" " INDEX (`.sparse_sim`) USING HASH,\n" " INDEX (`.dense_sim`) USING HASH\n" - ") ENGINE=MEMORY\n" + ") ENGINE=MEMORY COMMENT='Event counts and sums of (squared) spectral weights for selected Monte Carlo data binned in log10 energy'\n" "AS\n" "(\n" - " WITH Table0 AS\n" + " WITH BinnedData AS\n" " (\n" " SELECT\n" @@ -1009,5 +1009,5 @@ " SUM(POW(SpectralWeight,2)) AS SumW2\n" " FROM\n" - " Table0\n" + " BinnedData\n" " GROUP BY\n" " `.theta`, `.sparse_sim`, `.dense_sim`\n" @@ -1072,5 +1072,11 @@ query6 << "CREATE TEMPORARY TABLE ThetaDist\n" - "ENGINE=MEMORY\n" + "(\n" + " CountN INT UNSIGNED NOT NULL,\n" + " ErrCountN DOUBLE NOT NULL,\n" + " ZdWeight DOUBLE NOT NULL,\n" + " ErrZdWeight DOUBLE NOT NULL,\n" + " INDEX (`.theta`) USING HASH\n" + ") ENGINE=MEMORY COMMENT='Event counts and sums of (squared) spectral weights for selected Monte Carlo data binned in theta'\n" "AS\n" "(\n" @@ -1180,16 +1186,12 @@ mysqlpp::Query query7(&connection); query7 << - "CREATE TEMPORARY TABLE Weighted%100:table (\n" - " `.theta` SMALLINT UNSIGNED NOT NULL,\n" - " `.sparse_sim` SMALLINT UNSIGNED NOT NULL,\n" - " `.dense_sim` SMALLINT UNSIGNED NOT NULL,\n" - " CountN DOUBLE NOT NULL,\n" - " SumW DOUBLE NOT NULL,\n" - " SumW2 DOUBLE NOT NULL,\n" - " INDEX (`.theta`) USING HASH,\n" - " INDEX (`.sparse_sim`) USING HASH,\n" - " INDEX (`.dense_sim`) USING HASH\n" + "CREATE TEMPORARY TABLE Weighted%100:table\n" + "(\n" + " SumW2 DOUBLE NOT NULL,\n" + " INDEX (`.theta`) USING HASH,\n" + " INDEX (`.sparse_sim`) USING HASH,\n" + " INDEX (`.dense_sim`) USING HASH\n" ")\n" - "ENGINE=MEMORY\n" + "ENGINE=MEMORY COMMENT='Table Summary%100:table but with theta-weights applied'\n" "AS\n" "(\n" @@ -1282,5 +1284,24 @@ ") ENGINE=Memory\n" */ - "CREATE TEMPORARY TABLE AnalysisMC ENGINE=MEMORY\n" + "CREATE TEMPORARY TABLE AnalysisMC\n" + "(\n" + " SignalN INT UNSIGNED NOT NULL,\n" + " SignalW DOUBLE NOT NULL,\n" + " SignalW2 DOUBLE NOT NULL,\n" + " BackgroundN INT UNSIGNED NOT NULL,\n" + " BackgroundW DOUBLE NOT NULL,\n" + " BackgroundW2 DOUBLE NOT NULL,\n" + " ResidualW DOUBLE NOT NULL,\n" + " ResidualW2 DOUBLE NOT NULL,\n" + " SumEnergySimW DOUBLE NOT NULL,\n" + " SumEnergyEstW DOUBLE NOT NULL,\n" + " INDEX (`.theta`) USING HASH,\n" + " INDEX (`.sparse_est`) USING HASH,\n" + " INDEX (`.sparse_sim`) USING HASH,\n" + " INDEX (`.dense_est`) USING HASH,\n" + " INDEX (`.dense_sim`) USING HASH,\n" + " INDEX (`.sparse_est`, `.sparse_sim`) USING HASH,\n" + " INDEX (`.dense_est`, `.dense_sim`) USING HASH\n" + ") ENGINE=MEMORY COMMENT='Sum of counts and (squared) weightes of Monte Carlo Data after analysis'\n" "AS\n" "(\n" @@ -1358,5 +1379,93 @@ sindent indent9(query9); query9 << - "CREATE TEMPORARY TABLE %100:table ENGINE=MEMORY " + "CREATE TEMPORARY TABLE %100:table\n" + "(\n" + " SimCountN INT UNSIGNED NOT NULL,\n" + " TrigCountN INT UNSIGNED NOT NULL,\n" + " SignalN INT UNSIGNED NOT NULL,\n" + " BackgroundN DOUBLE NOT NULL,\n" + // + " ErrSimCountN DOUBLE NOT NULL,\n" + " ErrTrigCountN DOUBLE NOT NULL,\n" + " ErrSignalN DOUBLE NOT NULL,\n" + " ErrBackgroundN DOUBLE NOT NULL,\n" + // + " SimSumW DOUBLE NOT NULL,\n" + " TrigSumW DOUBLE NOT NULL,\n" + " SignalW DOUBLE NOT NULL,\n" + " BackgroundW DOUBLE NOT NULL,\n" + " ExcessW DOUBLE NOT NULL,\n" + // + " SimSumW2 DOUBLE NOT NULL,\n" + " TrigSumW2 DOUBLE NOT NULL,\n" + " SignalW2 DOUBLE NOT NULL,\n" + " BackgroundW2 DOUBLE NOT NULL,\n" + " ExcessW2 DOUBLE NOT NULL,\n" + // + " SimFluxW DOUBLE NOT NULL,\n" + " TrigFluxW DOUBLE NOT NULL,\n" + " SignalFluxW DOUBLE NOT NULL,\n" + " BackgroundFluxW DOUBLE NOT NULL,\n" + " ExcessFluxW DOUBLE NOT NULL,\n" + // + " ErrSimFluxW DOUBLE NOT NULL,\n" + " ErrTrigFluxW DOUBLE NOT NULL,\n" + " ErrSignalFluxW DOUBLE NOT NULL,\n" + " ErrBackgroundFluxW DOUBLE NOT NULL,\n" + " ErrExcessFluxW DOUBLE NOT NULL,\n" + // + " ResidualW DOUBLE NOT NULL,\n" + " ResidualW2 DOUBLE NOT NULL,\n" + " BiasW DOUBLE NOT NULL,\n" + " ErrBiasW DOUBLE NOT NULL,\n" + " ResolutionW DOUBLE,\n" + // + " SumEnergyEstW DOUBLE NOT NULL,\n" + " SumEnergySimW DOUBLE NOT NULL,\n" + // + " AvgEnergyEstW DOUBLE NOT NULL,\n" + " AvgEnergySimW DOUBLE NOT NULL,\n" + // + " CutEfficiencyN DOUBLE NOT NULL,\n" + " CutEfficiencyW DOUBLE NOT NULL,\n" + " TriggerEfficiencyN DOUBLE NOT NULL,\n" + " TriggerEfficiencyW DOUBLE NOT NULL,\n" + " EffectiveAreaN DOUBLE NOT NULL,\n" + " EffectiveAreaW DOUBLE NOT NULL,\n" + // + " ErrCutEfficiencyN DOUBLE NOT NULL,\n" + " ErrCutEfficiencyW DOUBLE NOT NULL,\n" + " ErrEffectiveAreaN DOUBLE NOT NULL,\n" + " ErrEffectiveAreaW DOUBLE NOT NULL,\n" + " ErrTriggerEfficiencyN DOUBLE NOT NULL,\n" + " ErrTriggerEfficiencyW DOUBLE NOT NULL,\n" + // + " IntegralSimFluxW DOUBLE NOT NULL,\n" + " IntegralSimFluxW2 DOUBLE NOT NULL,\n" + " IntegralSignalW DOUBLE NOT NULL,\n" + " IntegralSignalFluxW DOUBLE NOT NULL,\n" + " IntegralSignalFluxW2 DOUBLE NOT NULL,\n" + " IntegralBackgroundFluxW DOUBLE NOT NULL,\n" + " IntegralBackgroundFluxW2 DOUBLE NOT NULL,\n" + " IntegralExcessFluxW DOUBLE NOT NULL,\n" + // + " ErrIntegralExcessFluxW DOUBLE NOT NULL,\n" + " ErrIntegralSignalFluxW DOUBLE NOT NULL,\n" + " ErrIntegralBackgroundFluxW DOUBLE NOT NULL,\n" + " ErrIntegralSimFluxW DOUBLE NOT NULL,\n" + // + " IntegralEnergySimW DOUBLE NOT NULL,\n" + " IntegralEnergyEstW DOUBLE NOT NULL,\n" + // + " AvgIntegralEnergyEstW DOUBLE NOT NULL,\n" + " AvgIntegralEnergySimW DOUBLE NOT NULL,\n" + // + " ObsTime DOUBLE NOT NULL,\n" + " Area DOUBLE NOT NULL,\n" + " AreaTime DOUBLE NOT NULL,\n" + " Width DOUBLE NOT NULL,\n" + // + " INDEX (%102:bin) USING HASH\n" + ") ENGINE=MEMORY COMMENT='Summary of all Monte Carlo quantities, binned in true energy or zenith angle'\n" "AS\n" "(\n" @@ -1530,5 +1639,69 @@ sindent indent10(query10); query10 << - "CREATE TEMPORARY TABLE SummaryEstimatedEnergy_%100:binning ENGINE=MEMORY " + "CREATE TEMPORARY TABLE SummaryEstimatedEnergy_%100:binning\n" + "(\n" + " SignalN INT UNSIGNED NOT NULL,\n" + " BackgroundN DOUBLE NOT NULL,\n" + " ExcessN DOUBLE NOT NULL,\n" + // + " ErrSignalN DOUBLE NOT NULL,\n" + " ErrBackgroundN DOUBLE NOT NULL,\n" + " ErrExcessN DOUBLE NOT NULL,\n" + // + " SignalW DOUBLE NOT NULL,\n" + " BackgroundW DOUBLE NOT NULL,\n" + " ExcessW DOUBLE NOT NULL,\n" + // + " SignalW2 DOUBLE NOT NULL,\n" + " BackgroundW2 DOUBLE NOT NULL,\n" + // + " ErrSignalW DOUBLE NOT NULL,\n" + " ErrBackgroundW DOUBLE NOT NULL,\n" + " ErrExcessW DOUBLE NOT NULL,\n" + // + " SignalFluxW DOUBLE NOT NULL,\n" + " BackgroundFluxW DOUBLE NOT NULL,\n" + " ExcessFluxW DOUBLE NOT NULL,\n" + // + " ErrSignalFluxW DOUBLE NOT NULL,\n" + " ErrBackgroundFluxW DOUBLE NOT NULL,\n" + " ErrExcessFluxW DOUBLE NOT NULL,\n" + // + " ResidualW DOUBLE NOT NULL,\n" + " ResidualW2 DOUBLE NOT NULL,\n" + " BiasW DOUBLE NOT NULL,\n" + " ErrBiasW DOUBLE NOT NULL,\n" + " ResolutionW DOUBLE,\n" + // + " SumEnergyEstW DOUBLE NOT NULL,\n" + " SumEnergySimW DOUBLE NOT NULL,\n" + // + " AvgEnergyEstW DOUBLE NOT NULL,\n" + " AvgEnergySimW DOUBLE NOT NULL,\n" + // + " IntegralSignalW DOUBLE NOT NULL,\n" + " IntegralSignalFluxW DOUBLE NOT NULL,\n" + " IntegralSignalFluxW2 DOUBLE NOT NULL,\n" + " IntegralBackgroundFluxW DOUBLE NOT NULL,\n" + " IntegralBackgroundFluxW2 DOUBLE NOT NULL,\n" + " IntegralExcessFluxW DOUBLE NOT NULL,\n" + // + " ErrIntegralExcessFluxW DOUBLE NOT NULL,\n" + " ErrIntegralSignalFluxW DOUBLE NOT NULL,\n" + " ErrIntegralBackgroundFluxW DOUBLE NOT NULL,\n" + // + " IntegralEnergySimW DOUBLE NOT NULL,\n" + " IntegralEnergyEstW DOUBLE NOT NULL,\n" + // + " AvgIntegralEnergyEstW DOUBLE NOT NULL,\n" + " AvgIntegralEnergySimW DOUBLE NOT NULL,\n" + // + " ObsTime DOUBLE NOT NULL,\n" + " Area DOUBLE NOT NULL,\n" + " AreaTime DOUBLE NOT NULL,\n" + " Width DOUBLE NOT NULL,\n" + // + " INDEX (`.%100:binning:_est`) USING HASH\n" + ") ENGINE=MEMORY COMMENT='Summary of all Monte Carlo quantities binned in estimated energy'\n" "AS\n" "(\n" @@ -1636,5 +1809,8 @@ mysqlpp::Query query11(&connection); query11 << - "CREATE TEMPORARY TABLE EnergyMigration_%100:binning ENGINE=MEMORY\n" + "CREATE TEMPORARY TABLE EnergyMigration_%100:binning\n" + "(\n" + " SignalN INT UNSIGNED NOT NULL\n" + ") ENGINE=MEMORY COMMENT='Energy Migration: Monte Carlo Event counts binned in true and estimated energy'\n" "AS\n" "(\n" @@ -1699,8 +1875,8 @@ // ------------------------------------------------------------------- - // --------------------------- SummaryData --------------------------- - // ------------------------------------------------------------------- - - cout << separator("SummaryData") << '\n'; + // --------------------------- AnalysisData -------------------------- + // ------------------------------------------------------------------- + + cout << separator("AnalysisData") << '\n'; Time start12; @@ -1709,17 +1885,13 @@ sindent indent12(query12); query12 << - "CREATE TEMPORARY TABLE SummaryData ENGINE=MEMORY\n" -/* "(\n" - " `.theta` SMALLINT UNSIGNED NOT NULL,\n" - " `.sparse_est` SMALLINT UNSIGNED NOT NULL,\n" - " `Signal` DOUBLE NOT NULL,\n" - " `ErrSignal` DOUBLE NOT NULL,\n" - " `Background` DOUBLE NOT NULL,\n" - " `ErrBackground` DOUBLE NOT NULL,\n" - " `Excess` DOUBLE NOT NULL,\n" - " `ErrExcess` DOUBLE NOT NULL,\n" - " `Significance` DOUBLE NOT NULL,\n" - " PRIMARY KEY (`.sparse_est`) USING HASH\n" - ") ENGINE=Memory\n"*/ + "CREATE TEMPORARY TABLE AnalysisData\n" + "(\n" + " `Signal` INT UNSIGNED NOT NULL,\n" + " `Background` INT UNSIGNED NOT NULL,\n" + " `SumEnergyEst` DOUBLE NOT NULL,\n" + " `SumW` DOUBLE NOT NULL,\n" + " INDEX (`.theta`) USING HASH,\n" + " INDEX (`.sparse_est`) USING HASH\n" + ") ENGINE=MEMORY COMMENT='Sum of counts and (squared) weightes of selected data after analysis'\n" "AS\n" "(\n" @@ -1758,5 +1930,5 @@ cout << query12.execute().info() << endl; ShowWarnings(connection); - Dump(flog, connection, "SummaryData"); + Dump(flog, connection, "AnalysisData"); const auto sec12 = Time().UnixTime()-start12.UnixTime(); @@ -1777,5 +1949,4 @@ Time start13; - /* "CREATE TEMPORARY TABLE Spectrum\n" @@ -1817,6 +1988,5 @@ sindent indent13(query13); query13 << - "CREATE TEMPORARY TABLE %100:table ENGINE=MEMORY " - "AS\n" + "CREATE TEMPORARY TABLE %100:table ENGINE=MEMORY COMMENT='Combined information from different sources into final spectrum' AS\n" "(\n" << indent(3) << spectrum_sql << indent(0) <<