source: trunk/DataCheck/Tools/prepare_db.sh@ 19971

#!/bin/bash

# setup
sqlpw=/home/$USER/.mysql.pw
dbname=factdata20170804

# DB files 
dbfile=/home/dorner/fact_db/factdata_2017-08-04.sql
dbfileold=/home/dorner/fact_db/factdata_cp20150811.sql

# cu-function (QLA) 
cuqla="CREATE FUNCTION CUQLA(n int) RETURNS double DETERMINISTIC RETURN 
   IF (n<20140520, 16.4, IF (n<20150131, 25.2, IF (n<20150716, 12.5, 
   IF (n<20160218, 20.0, IF (n<20160901, 15.1, 16.8)))))" 
# define CUISDC accordingly

# mjd-function
mjd="CREATE FUNCTION MJD(t datetime) RETURNS double DETERMINISTIC RETURN
   ROUND(UNIX_TIMESTAMP(CONVERT_TZ(t, '+00:00', 'SYSTEM'))/86400.+40587,9) "

lima="CREATE FUNCTION LiMa(sig double, bg double) RETURNS double DETERMINISTIC
   RETURN  IF (sig>0 AND bg*5>0 AND (sig*Log(sig/(sig+bg*5)*1.2/0.2)+bg*5*Log(bg*5/(sig+bg*5)*1.2))> 0,
   Sqrt((sig*Log(sig/(sig+bg*5)*1.2/0.2)+bg*5*Log(bg*5/(sig+bg*5)*1.2))*2), 0) "
   
excerr="CREATE FUNCTION ExcErr(sig double, bg double) RETURNS double DETERMINISTIC 
   RETURN IF ((sig + 0.2*0.2*bg*5)<0, 0, Sqrt(sig + 0.2*0.2*bg*5)) "

combinedtable="create table AnalysisResultsAllQLA like AnalysisResultsRunLP"

# set up DB
echo "read-in DB-copy $dbfile to DB $dbname..."
cat $dbfile | sed "s/factdata/$dbname/g" | mysql --defaults-file=$sqlpw -u root
# create functions
# (not needed if same computer and DB only updated)
echo "Creating funciton CUQLA..."
mysql --defaults-file=$sqlpw -u root $dbname -s -e "$cuqla" 
echo "Creating funciton Mjd..."
mysql --defaults-file=$sqlpw -u root $dbname -s -e "$mjd" 
echo "Creating funciton LiMa..."
mysql --defaults-file=$sqlpw -u root $dbname -s -e "$lima" 
echo "Creating funciton ExcErr..."
mysql --defaults-file=$sqlpw -u root $dbname -s -e "$excerr" 

# create table with full QLA content
echo "create and populate table with full QLA results..."
drop="drop table if exists AnalysisResultsAllQLA"
create="create table AnalysisResultsAllQLA like AnalysisResultsRunLP"
copyqla="insert into AnalysisResultsAllQLA select * from AnalysisResultsRunLP"
copyold="insert into AnalysisResultsAllQLA select fRunID, fNight, fNumEvtsAfterCleaning, "
copyold=$copyold"fNumEvtsAfterQualCuts, fNumEvtsAfterBgCuts, fNumBgEvts, fNumSigEvts, fNumExcEvts, "
copyold=$copyold"fNumIslandsMean, fOnTimeAfterCuts, Now() from factdata_cp20150811.AnalysisResultsRunISDC fcp "
copyold=$copyold"where fNight<20121213"
mysql --defaults-file=$sqlpw -u root $dbname -s -e "$drop" 
mysql --defaults-file=$sqlpw -u root $dbname -s -e "$create" 
mysql --defaults-file=$sqlpw -u root $dbname -s -e "$copyqla" 
mysql --defaults-file=$sqlpw -u root $dbname -s -e "$copyold" 

echo "done. Please check if table AnalysisResultsAllQLA is complete."

checkdb="SELECT MIN(fNight), MAX(fNight), COUNT(*), COUNT(DISTINCT fNight) FROM "$dbname".AnalysisResultsAllQLA"
mysql --defaults-file=$sqlpw -u root $dbname -e "$checkdb" 

# create functions
exit

# alternative way for CU-Function

# creat table CU: 
createcu="CREATE TABLE CU (fNight int(10) unsigned DEFAULT NULL,
  fCU double(3,1) DEFAULT NULL, fLastUpdate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  fValid tinyint(3) unsigned DEFAULT NULL, fAnalysis tinyint(3) unsigned DEFAULT '1')"

# from 9.6.2017
#mysql> select * from CU; 
#+----------+------+---------------------+--------+-----------+
#| fNight   | fCU  | fLastUpdate         | fValid | fAnalysis |
#+----------+------+---------------------+--------+-----------+
# --- first iteration of CU: 
#| 20111115 | 16.5 | 2017-05-30 20:50:15 |   NULL |         1 |
#| 20140520 | 24.0 | 2017-05-30 20:50:15 |   NULL |         1 |
#| 20150131 | 16.0 | 2017-05-30 20:50:15 |   NULL |         1 |
#| 20150716 | 20.0 | 2017-05-30 20:50:15 |   NULL |         1 |
#| 20160218 | 15.0 | 2017-05-30 20:50:15 |   NULL |         1 |
#| 20160901 | 16.5 | 2017-05-30 20:50:15 |   NULL |         1 |
# --- next iteration of CU: 
#| 20121212 | 16.4 | 2017-05-30 21:07:26 |      1 |         1 |
#| 20140526 | 25.2 | 2017-05-30 21:08:28 |      1 |         1 |
#| 20150131 | 12.5 | 2017-05-30 21:08:51 |      1 |         1 |
#| 20150716 | 20.0 | 2017-05-30 21:09:09 |      1 |         1 |
#| 20160218 | 15.1 | 2017-05-30 21:09:42 |      1 |         1 |
#| 20160901 | 16.8 | 2017-05-30 21:10:15 |      1 |         1 |
#+----------+------+---------------------+--------+-----------+
#12 rows in set (0,00 sec)

cuinsert="INSERT CU VALUES 
   (20121212, 16.4, '2017-05-30 21:07:26', 1, 1)
   (20140526, 25.2, '2017-05-30 21:07:26', 1, 1)
   (20150131, 12.5, '2017-05-30 21:07:26', 1, 1)
   (20150716, 20.0, '2017-05-30 21:07:26', 1, 1)
   (20160218, 15.1, '2017-05-30 21:07:26', 1, 1)
   (20160901, 16.8, '2017-05-30 21:07:26', 1, 1) "

delstart=" delimiter $$ "
delend=" delimiter ; "
cufkt="create function CU(night int, ana tinyint) returns double(4,1) deterministic begin declare cu double; set cu=0; select fCU into cu from CU where fValid=1 and fAnalysis=ana and fNight<night order by fNight desc limit 0,1; return cu; end $$ "