\documentclass{article}
\usepackage[utf8]{inputenc}
\usepackage[T1]{fontenc}
\usepackage{amsmath}
\usepackage{amssymb}
\usepackage{mathrsfs}
\usepackage{longtable}
%\usepackage{multicol}
\usepackage[table]{xcolor}
\usepackage[top=2cm, bottom=2cm, left=2cm, right=2cm]{geometry}
\usepackage{multirow}
\usepackage{alltt}
%\usepackage{capt-of}
\usepackage{filecontents}
\usepackage[pdfborder={0 0 0}]{hyperref}
\usepackage[backend=biber]{biblatex}
%\usepackage{graphicx}
%\usepackage{epstopdf}
\usepackage{float}
\usepackage{lmodern}
\usepackage{upquote}
\usepackage{minted}
\usemintedstyle{github}
\usepackage{tkz-graph}
\usetikzlibrary{arrows}
\usepackage{booktabs}

\newcolumntype{g}{>{\columncolor{green!20}}l}

\AtBeginDocument{%
\def\PYZsq{\textquotesingle}%
}
%\newsavebox\ltmcbox

\title{Kronos data processing Pipeline and Database\\
    Preliminary version}
\author{Henri Chain}

\begin{filecontents}{biblio_kronos.bib}
@misc{control,
    author = {Zarka, P. and Cecconi, B. and Vogl, D. and Boisnard, G.},
    year = {2008},
    title = {CASSINI-RPWS-HFR / KRONOS DATA in Meudon, version 1.6},
    url = {http://bit.ly/1scVkm9}
}

\end{filecontents}

\addbibresource{biblio_kronos.bib}

\begin{document}
    \maketitle
    \section{Kronos Data Products}
        % \begin{center}
        % \begin{tikzpicture}[->,>=stealth',shorten >=1pt,auto,node distance=3.5cm,
        %                     thick,main node/.style={draw}]
        %     \node[main node] (k) {k files};
        %     \node[main node] (n1) [right of=k] {n1 files};
        %     \node[main node] (n2) [right of=n1] {n2 files};
        %     \node[main node] (n3) [right of=n2] {n3(b,c,d,e,f) files};
        %     \node[main node] (ephem) [below of=n2] {ephemeris files};

        %     \path[every node/.style={font=\sffamily\small}]
        %     (k) edge node {rearrange} (n1)
        %     (n1) edge node {calibrate} (n2)
        %     (n2) edge node {inversion} (n3)
        %     (ephem) edge node {uses} (n3);

        % \end{tikzpicture}
        % \end{center}
        \begin{figure}[H]
            \begin{center}
                \includegraphics[width=\textwidth]{data_products.pdf}
            \end{center}
            \caption{Kronos Data Flow}
        \end{figure}
    \section{Ingesting Kronos files into a database}
        \subsection{\texttt{kronos\_sources} table}
            \begin{table}[H]
            \centering
            \begin{tabular}{lll}
                \toprule
                \textbf{Name} & \textbf{Type} & \textbf{Comment} \\
                \midrule
                \rowcolor{green!15}\texttt{source} & \texttt{char(2)} & Two-letter code of the ephemeris source (\texttt{sc}, \texttt{sq}...). Unique.\\
                \texttt{name} & \texttt{varchar(128)} & Full Name of source. \\
                \texttt{need} & \texttt{char(2)} & Optional source dependency. (can be \texttt{NULL})\\
                \texttt{att} & \texttt{char(4)} & Four-letter attitude extension  (\texttt{qsse}, \texttt{qssq})\\
                \texttt{eph} & \texttt{char(4)} & Four-letter position extension  (\texttt{vsat}, \texttt{vear})\\
                \texttt{body} & \texttt{varchar(128)} & Body name  (\texttt{Saturn}, \texttt{Earth}...)\\
                \texttt{radius} & \texttt{real} & Body radius (km) \\
                %#  uxse = 4 lower-case ref frame code name 
                %#         (see attitude server source code page, 'entry4' tag)
                %#  coor = 4 lower-case coordinate system frame name
                %#    (see ephemeris server source code page, 'coordinates' tag)
                %#  orig = 4 lower-case source code name 
                %#         (see attitude/ephemeris server source code page, 'orig' tag)
                %#  optionaly specify the position of the source 
                %#     (default is position of orig)
                %#  xx = X position of source in body raduis and uxse
                %#  yy = Y position of source in body raduis and uxse
                %#  zz = Z position of source in body raduis and uxse
            \bottomrule
            \end{tabular}
            \caption{\texttt{kronos\_sources} schema}
            \end{table}
            \begin{table}[H]
            \centering
            \begin{tabular}{lllllll}
                \toprule
                \textbf{\texttt{source}} & \textbf{\texttt{name}} & \textbf{\texttt{need}} & \textbf{\texttt{att}} & \textbf{\texttt{eph}} & \textbf{\texttt{body}} & \textbf{\texttt{radius}} \\
                \midrule
                \texttt{su} & Sun & & \texttt{qsun} & \texttt{vsun} & Sun & 695500 \\
                \texttt{ea} & Earth & & \texttt{qgse} & \texttt{vear} & Earth & 6378.14 \\
                \texttt{ju} & Jupiter & & \texttt{qjse} & \texttt{vjup} & Jupiter & 71492 \\
                \texttt{sc} & Saturn (Center) - ecliptic frame & & \texttt{qsse} & \texttt{vsat} & Saturn & 60268 \\
                \texttt{sq} & Saturn (Center) - equatorial frame & & \texttt{qssq} & \texttt{vsat} & Saturn & 60268 \\
                \texttt{sj} & Saturn (Barycenter) - J2000 frame & & \texttt{qj20} & \texttt{vstb} & Saturn & 60268 \\
                \texttt{ti} & Titan - ecliptic frame & \texttt{sc} & \texttt{qsse} & \texttt{vtit} & Titan & 2575 \\
                \texttt{tq} & Titan - equatorial frame & \texttt{sq} & \texttt{qssq} & \texttt{vtit} & Titan & 2575 \\
                %\texttt{-}\texttt{-} & Dummy Source & & & & & \\
                %\hline
            \bottomrule
            \end{tabular}
            \caption{\texttt{kronos\_sources} typical contents}
            \end{table}
        \subsection{\texttt{kronos\_antennae} table}
            \begin{table}[H]
            \centering
            \begin{tabular}{lll}
                \toprule
                \textbf{Name} & \textbf{Type} & \textbf{Comment} \\
                \midrule
                \rowcolor{green!15}\texttt{ant} & \texttt{char(1)} & One-letter code of the antenna parameter set (\texttt{d}, \texttt{j}, \texttt{r}...). Unique.\\
                \texttt{name} & \texttt{varchar(128)} & Full Name of antenna set. \\
                \texttt{filename} & \texttt{varchar(128)} & Name of antenna file in \texttt{/ant} directory. \\
            \bottomrule
            \end{tabular}
            \caption{\texttt{kronos\_antennae} schema}
            \end{table}
            \begin{table}[H]
            \centering
            \begin{tabular}{lll}
                \toprule
                \textbf{\texttt{ant}} & \textbf{\texttt{name}} & \textbf{\texttt{filename}}\\
                \midrule
                \texttt{j} & Jupiter-Cal(calJGR2004) & \texttt{calJGR2004} \\
                \texttt{d} & December-04-cal & \texttt{calDec04} \\
                \texttt{r} & Rheometry & \texttt{rheometry.ant} \\
                \texttt{x} & Default Calibration & \\
            \bottomrule
            \end{tabular}
            \caption{\texttt{kronos\_antennae} typical contents}
            \end{table}
        \subsection{\texttt{kronos\_mfl} table}
            \begin{table}[H]
            \centering
            \begin{tabular}{lll}
                \toprule
                \textbf{Name} & \textbf{Type} & \textbf{Comment} \\
                \midrule
                \rowcolor{green!15}\texttt{model} & \texttt{varchar(32)} & Magnetic field model code (\texttt{SPV}, \texttt{VIP4}, \texttt{SPVR}...). Unique. \\
                \texttt{name} & \texttt{varchar(128)} & Description of model. \\
                \texttt{body} & \texttt{varchar(128)} & Body Name. \\
                \texttt{filename} & \texttt{varchar(128)} & Name of stored \texttt{iso\_fc\_grid} file in \texttt{/rpws/bin/} directory. \\
            \bottomrule
            \end{tabular}
            \caption{\texttt{kronos\_mfl} schema}
            \end{table}
            \begin{table}[H]
            \centering
            \begin{tabular}{llll}
                \toprule
                \textbf{\texttt{model}} & \textbf{\texttt{name}} & \textbf{\texttt{body}} & \textbf{\texttt{filename}} \\
                \midrule
                \texttt{D} & 7 Gauss (Z96) model & Jupiter &  \\
                \texttt{O6} & $O_6$ Octupole model (Connerney et al. 1993) & Jupiter &   \\
                \texttt{VIP4} & VIP4 model (Connerney et al. 1998) & Jupiter &   \\
                \texttt{VIT4} & VIT4 model (Connerney et al. 2007) & Jupiter &  \\
            \texttt{Z3} & $Z_3$ model (Connerney et al. 1982) & Saturn &   \\
                \texttt{SPV} & Saturn Pioneer Voyager model (David and Smith, 1990) & Saturn & \texttt{iso\_fc\_grid\_SPV.sav}  \\
                \texttt{SPVR} & SPV-R model, 5th order truncated & Saturn &  \\
            \bottomrule
            \end{tabular}
            \caption{\texttt{kronos\_mfl} typical contents}
            \end{table}
        \subsection{\texttt{kronos\_periods} table}
        \footnotetext[1]{\texttt{NULL} if unavailable or of size 0}
            \begin{table}[H]
            \centering
            \begin{tabular}{lll}
                \toprule
                \textbf{Name} & \textbf{Type} & \textbf{Comment} \\
                \midrule
                \rowcolor{green!15}\texttt{period} & \texttt{varchar(128)} & Name of the data directory (\texttt{2013\_091\_180}, \texttt{Venus1}...). Unique. \\
                \texttt{start\_year} & \texttt{integer} & Year of first day in period. 1997 to 2017.\\
                \texttt{start\_day} & \texttt{integer} & First day in period. 001 to 366.\\
                \texttt{end\_year} & \texttt{integer} & Year of last day in period. 1997 to 2017.\\
                \texttt{end\_day} & \texttt{integer} & Last day in period. 001 to 366.\\
                \texttt{bg} & \texttt{timestamp} & Date the background file was last computed\footnotemark[1] (\texttt{/bg/bg\_yyyyddd\_yyyyddd}) \\
                \texttt{bg\_tmp} & \texttt{timestamp} & Timestamp of temporary background file\footnotemark[1] (\texttt{/bg/bg\_yyyyddd\_yyyyddd\_tmp}) \\
            \bottomrule
            \end{tabular}
            \caption{\texttt{kronos\_periods} schema}
            \end{table}
        \subsection{\texttt{kronos\_days} table}
            \begin{table}[H]
            \centering
            \begin{tabular}{lll}
                \toprule
                \textbf{Name} & \textbf{Type} & \textbf{Comment} \\
                \midrule
                \rowcolor{green!15}\texttt{year} & \texttt{integer} & 1997 to 2017 \\
                \rowcolor{green!15}\texttt{day} & \texttt{integer} & 001 to 366, (\texttt{year}, \texttt{day}) must be unique.\\
                \texttt{period} & \texttt{varchar(128)} & Must be present in \texttt{kronos\_periods} \\
                %\texttt{k} & \texttt{boolean} & Whether all hourly files have been downloaded \\
                %\hline
                %\texttt{n1} & \texttt{boolean} & Whether all hourly files are available at \texttt{n1} level. \\
                %\hline
                %\texttt{n2} & \texttt{boolean} & Whether all hourly files are available at \texttt{n2} level. \\
                %\hline
                %\texttt{n3} & \texttt{boolean} & Whether all hourly files are available at \texttt{n3} level. (not available yet) \\
                %\hline
                %\texttt{n3a} & \texttt{boolean} & Whether all hourly files are available at \texttt{n3a} level. (unused) \\
                %\hline
                %\texttt{n3b} & \texttt{boolean} & Whether all hourly files are available at \texttt{n3b} level. \\
                %\hline
                %\texttt{n3c} & \texttt{boolean} & Whether all hourly files are available at \texttt{n3c} level. \\
                %\hline
                %\texttt{n3d} & \texttt{boolean} & Whether all hourly files are available at \texttt{n3d} level. \\
                %\hline
                %\texttt{n3e} & \texttt{boolean} & Whether all hourly files are available at \texttt{n3e} level. \\
                %\hline
                %\texttt{n3f} & \texttt{boolean} & Whether all hourly files are available at \texttt{n3f} level. \\
                %\hline
                %\texttt{n3g} & \texttt{boolean} & Whether all hourly files are available at \texttt{n3g} level. \\
                %\hline
                \texttt{source} & \texttt{char(2)} & Source used for plots. Must be present in \texttt{kronos\_sources}. \\
                \texttt{ant} & \texttt{char(1)} & Antenna set used for plots. Must be present in \texttt{kronos\_antennae}. \\
                \texttt{raw} & \texttt{timestamp} & Date the daily Raw plot was last computed\footnotemark[1] (\texttt{/pdf/yyyyddd.pdf}) \\
                \texttt{sve} & \texttt{timestamp} & Date the daily 2-Antenna $S/V$ plot was last computed\footnotemark[1] (\texttt{/pdf/yyyyddd-SVe.pdf}) \\
                \texttt{svb} & \texttt{timestamp} & Date the daily 3-Antenna $S/V$ plot was last computed\footnotemark[1] (\texttt{/pdf/yyyyddd-SVb.pdf}) \\
                \texttt{ltb} & \texttt{timestamp} & Date the daily 3-Antenna $L/T$ plot was last computed\footnotemark[1] (\texttt{/pdf/yyyyddd-LTb.pdf}) \\
                \texttt{lis} & \texttt{timestamp} & Date the daily \texttt{.lis} file was last computed\footnotemark[1] (\texttt{/lis/yyyyddd.lis}) \\
                \texttt{skr} & \texttt{timestamp} & Date the daily \texttt{.skr} file was last computed\footnotemark[1] (\texttt{/skr/yyyyddd.skr}) \\
            \bottomrule
            \end{tabular}
            \caption{\texttt{kronos\_days} schema}
            \end{table}
        \subsection{\texttt{kronos\_ephem} table}
            \begin{table}[H]
            \centering
            \begin{tabular}{lll}
                \toprule
                \textbf{Name} & \textbf{Type} & \textbf{Comment} \\
                \midrule
                \rowcolor{green!15}\texttt{year} & \texttt{integer} & 1997 to 2017 \\
                \rowcolor{green!15}\texttt{day} & \texttt{integer} & 001 to 366, (\texttt{year}, \texttt{day}) must be present in \texttt{kronos\_days}.\\
                \rowcolor{green!15}\texttt{source} & \texttt{char(2)} & Must be present in \texttt{kronos\_sources}. \\
                \texttt{att} & \texttt{timestamp} & Date the hourly file was last downloaded\footnotemark[1]  (\texttt{/ephem/yyyyddd.qxxx})\\
                \texttt{eph} & \texttt{timestamp} & Date the hourly file was last downloaded\footnotemark[1]  (\texttt{/ephem/yyyyddd.vyyy})\\
            \bottomrule
            \end{tabular}
            \caption{\texttt{kronos\_ephem} schema}
            \end{table}
            (\texttt{year}, \texttt{day}, \texttt{source}) must be unique.
        \footnotetext[2]{Relative to \url{http://cassini.physics.uiowa.edu/~wsk/cas/daspage/cas_data/}}
        \subsection{\texttt{kronos\_2hours} table}
            This table contains timestamps for half-hourly data products (every two hours) as well as average ephemeris data.
            \begin{table}[H]
            \centering
            \begin{tabular}{lll}
                \toprule
                \textbf{Name} & \textbf{Type} & \textbf{Comment} \\
                \midrule
                \rowcolor{green!15}\texttt{year} & \texttt{integer} & 1997 to 2017 \\
                \rowcolor{green!15}\texttt{day} & \texttt{integer} & 001 to 366, (\texttt{year}, \texttt{day}) must be present in \texttt{kronos\_days}. \\
                \rowcolor{green!15}\texttt{hour} & \texttt{integer} & 00 to 22, even, (\texttt{year}, \texttt{day}, \texttt{hour}) must be unique. \\
                \texttt{img\_raw1} & \texttt{timestamp} & Date the raw plot image was last computed\footnotemark[1] (\texttt{/img/yyyyddd-1-hh.png}). \\
                \texttt{img\_raw2} & \texttt{timestamp} & Date the raw bg-subtracted plot image was last computed\footnotemark[1] (\texttt{/img/yyyyddd-2-hh.png}). \\
                \texttt{img\_sve1} & \texttt{timestamp} & Date the 2-Antenna $S$ plot image was last computed\footnotemark[1] (\texttt{/img/yyyyddd-SVe-1-hh.png}). \\
                \texttt{img\_sve2} & \texttt{timestamp} & Date the 2-Antenna $V$ plot image was last computed\footnotemark[1] (\texttt{/img/yyyyddd-SVe-2-hh.png}). \\
                \texttt{img\_svb1} & \texttt{timestamp} & Date the 3-Antenna $S$ plot image was last computed\footnotemark[1] (\texttt{/img/yyyyddd-SVb-1-hh.png}). \\
                \texttt{img\_svb2} & \texttt{timestamp} & Date the 3-Antenna $V$ plot image was last computed\footnotemark[1] (\texttt{/img/yyyyddd-SVb-2-hh.png}). \\
                \texttt{img\_ltb1} & \texttt{timestamp} & Date the 3-Antenna $L$ plot image was last computed\footnotemark[1] (\texttt{/img/yyyyddd-LTb-1-hh.png}). \\
                \texttt{img\_ltb2} & \texttt{timestamp} & Date the 3-Antenna $T$ plot image was last computed\footnotemark[1] (\texttt{/img/yyyyddd-LTb-2-hh.png}). \\
                \texttt{dist} & \texttt{real} & Average distance from ephemeris data (can be \texttt{NULL}). \\
                \texttt{ra} & \texttt{real} & Average local time from ephemeris data (can be \texttt{NULL}). \\
                \texttt{lat} & \texttt{real} & Average latitude from ephemeris data (can be \texttt{NULL}). \\
            \bottomrule
            \end{tabular}
            \caption{\texttt{kronos\_2hours} schema}
            \end{table}
        \subsection{\texttt{kronos\_hours} table}
            This table contains timestamps for hourly data products.
            \begin{table}[H]
            \centering
            \begin{tabular}{lll}
                \toprule
                \textbf{Name} & \textbf{Type} & \textbf{Comment} \\
                \midrule
                \rowcolor{green!15}\texttt{year} & \texttt{integer} & 1997 to 2017 \\
                \rowcolor{green!15}\texttt{day} & \texttt{integer} & 001 to 366, (\texttt{year}, \texttt{day}) must be present in \texttt{kronos\_days}. \\
                \rowcolor{green!15}\texttt{hour} & \texttt{integer} & 00 to 23, (\texttt{year}, \texttt{day}, \texttt{hour}) must be unique \\
                %\texttt{trimester} & \texttt{varchar(30)} & Name of the data directory (\texttt{2013\_091\_180}, \texttt{Venus1}...) \\
                %\hline
                \texttt{k} & \texttt{timestamp} & Date the hourly file was last downloaded\footnotemark[1] (\texttt{/k/Kyyyyddd.hh}) \\
                \texttt{bad} & \texttt{boolean} & Whether the \texttt{K} file is unusable (therefore located in \texttt{/k\_bad/Kyyyyddd.hh}) \\
                \texttt{src\_dir} & \texttt{varchar(128)} & Location of the \texttt{K} file on Iowa server \footnotemark[2]\\
                \texttt{n1} & \texttt{timestamp} & Date the hourly file was last computed\footnotemark[1] in level \texttt{n1} (\texttt{/n1/Ryyyyddd.hh}) \\
                \texttt{n2} & \texttt{timestamp} & Date the hourly file was last computed\footnotemark[1] in level \texttt{n2} (\texttt{/n2/Pyyyyddd.hh}) \\
                \texttt{n3g} & \texttt{timestamp} & Date the hourly file was last computed\footnotemark[1] in level \texttt{n3g} (\texttt{/n3g/Fyyyyddd.hh}) \\
                \texttt{index} & \texttt{timestamp} & Date the 2-Antenna index file was last computed\footnotemark[1] (\texttt{/index/INDEX\_yyyyddd.hh}) \\
                \texttt{index\_3a} & \texttt{timestamp} & Date the 3-Antenna index file was last computed\footnotemark[1] (\texttt{/index/INDEX\_3A\_yyyyddd.hh}) \\
            \bottomrule
            \end{tabular}
            \caption{\texttt{kronos\_hours} schema}
            \end{table}
        \subsection{\texttt{kronos\_loc} table}
            \begin{table}[H]
            \centering
            \begin{tabular}{lll}
                \toprule
                \textbf{Name} & \textbf{Type} & \textbf{Comment} \\
                \midrule
                \rowcolor{green!15}\texttt{year} & \texttt{integer} & 1997 to 2017 \\
                \rowcolor{green!15}\texttt{day} & \texttt{integer} & 001 to 366 \\
                \rowcolor{green!15}\texttt{hour} & \texttt{integer} & 00 to 23, (\texttt{year}, \texttt{day}, \texttt{hour}) must be present in \texttt{kronos\_hours}. \\
                \rowcolor{green!15}\texttt{mfl} & \texttt{varchar(32)} & Must be present in \texttt{kronos\_mfl}. \\
                \texttt{loc} & \texttt{timestamp} & Date the localization file was last computed\footnotemark[1] (\texttt{/loc/loc\_MFL\_yyyyddd.hh}) \\
                \texttt{loc\_3a} & \texttt{timestamp} & Modification date of 3-antenna \texttt{.loc} file\footnotemark[1] (\texttt{/loc/loc\_3A\_MFL\_yyyyddd.hh}) \\
            \bottomrule
            \end{tabular}
            \caption{\texttt{kronos\_loc} schema}
            \end{table}
            (\texttt{year}, \texttt{day}, \texttt{hour}, \texttt{mfl}) must be unique.
        \subsection{\texttt{kronos\_loc\_err} table}
            \begin{table}[H]
            \centering
            \begin{tabular}{lll}
                \toprule
                \textbf{Name} & \textbf{Type} & \textbf{Comment} \\
                \midrule
                \rowcolor{green!15}\texttt{year} & \texttt{integer} & 1997 to 2017 \\
                \rowcolor{green!15}\texttt{day} & \texttt{integer} & 001 to 366 \\
                \rowcolor{green!15}\texttt{hour} & \texttt{integer} & 00 to 23. \\
                \rowcolor{green!15}\texttt{mfl} & \texttt{varchar(32)} & (\texttt{year}, \texttt{day}, \texttt{hour}, \texttt{mfl}) must be present in \texttt{kronos\_loc}. \\
                \rowcolor{green!15}\texttt{n\_error} & \texttt{integer} & Number of error vectors. \\
                \texttt{loc\_err} & \texttt{timestamp} & Modification date\footnotemark[1] of \texttt{/loc/loc\_err\_MFL\_nNN\_yyyyddd.hh} \\
                \texttt{loc\_err\_3a} & \texttt{timestamp} & Modification date\footnotemark[1] of \texttt{/loc/loc\_err\_3A\_MFL\_nNN\_yyyyddd.hh} \\
            \bottomrule
            \end{tabular}
            \caption{\texttt{kronos\_loc} schema}
            \end{table}
            (\texttt{year}, \texttt{day}, \texttt{hour}, \texttt{mfl}, \texttt{n\_error}) must be unique.
        \subsection{\texttt{kronos\_n3} table}
            \begin{table}[H]
            \centering
            \begin{tabular}{lll}
                \toprule
                \textbf{Name} & \textbf{Type} & \textbf{Comment} \\
                \midrule
                \rowcolor{green!15}\texttt{year} & \texttt{integer} & 1997 to 2017 \\
                \rowcolor{green!15}\texttt{day} & \texttt{integer} & 001 to 366 \\
                \rowcolor{green!15}\texttt{hour} & \texttt{integer} & 00 to 23, (\texttt{year}, \texttt{day}, \texttt{hour}) must be present in \texttt{kronos\_hours}. \\
                \rowcolor{green!15}\texttt{source} & \texttt{char(2)} & (\texttt{year}, \texttt{day}, \texttt{source}) must be present in \texttt{kronos\_ephem}. \\
                \rowcolor{green!15}\texttt{ant} & \texttt{char(1)} & Must be present in \texttt{kronos\_antennae}. \\
                %\texttt{xyy} & \texttt{char(3)} & Three-letter code describing antenna parameter set and radiosource of reference \\
                %\hline
                \texttt{n3a} & \texttt{timestamp} & Date the hourly file was last computed\footnotemark[1] in level \texttt{n3a} (\texttt{/n3a/N3a\_xyyyyyyddd.hh}) \\
                \texttt{n3b} & \texttt{timestamp} & Date the hourly file was last computed\footnotemark[1] in level \texttt{n3b} (\texttt{/n3b/N3b\_xyyyyyyddd.hh}) \\
                \texttt{n3bfull} & \texttt{timestamp} & Same with all solutions (\texttt{/n3b/N3b-full\_xyyyyyyddd.hh}) \\
                \texttt{n3c} & \texttt{timestamp} & Date the hourly file was last computed\footnotemark[1] in level \texttt{n3c} (\texttt{/n3c/N3c\_xyyyyyyddd.hh}) \\
                \texttt{n3cfull} & \texttt{timestamp} & Same with all solutions (\texttt{/n3b/N3c-full\_xyyyyyyddd.hh}) \\
                \texttt{n3d} & \texttt{timestamp} & Date the hourly file was last computed\footnotemark[1] in level \texttt{n3d} (\texttt{/n3d/N3d\_xyyyyyyddd.hh}) \\
                \texttt{n3dfull} & \texttt{timestamp} & Same with all solutions (\texttt{/n3b/N3d-full\_xyyyyyyddd.hh}) \\
                \texttt{n3e} & \texttt{timestamp} & Date the hourly file was last computed\footnotemark[1] in level \texttt{n3e} (\texttt{/n3e/N3e\_xyyyyyyddd.hh}) \\
                \texttt{n3efull} & \texttt{timestamp} & Same with all solutions (\texttt{/n3b/N3e-full\_xyyyyyyddd.hh}) \\
                \texttt{n3f} & \texttt{timestamp} & Date the hourly file was last computed\footnotemark[1] in level \texttt{n3f} (\texttt{/n3f/N3f\_xyyyyyyddd.hh}) \\
                \texttt{n3} & \texttt{timestamp} & Date the hourly file was last computed\footnotemark[1] in level \texttt{n3} (not fully specified yet)\\
            \bottomrule
            \end{tabular}
            \caption{\texttt{kronos\_n3} schema}            
            \end{table}
            (\texttt{year}, \texttt{day}, \texttt{hour}, \texttt{source}, \texttt{ant}) must be unique.
    \section{Exploiting the database}
        We are going to use a library for convenient access to the database directly from \texttt{IDL}: \url{https://github.com/segasai/pg_idl} \\
        The project doesn't seem to be very active. \\
        2 compilation issues:
        \begin{enumerate}
            \item PostgreSQL and \texttt{pg\_idl} have to be built for 32 bits because IDL expects a 32 bit binary for modules as there is only a 32 bit IDL on Kronos.
            \item \texttt{pg\_idl} relies on IDL to build the module itself, and the parameters used by IDL are wrong, which results in a \texttt{"duplicate symbol \_kw"} error, because the flag \texttt{-fno-common} was set.
        \end{enumerate}
        First compile your pgsql (or modify the pg\_idl to include the right configure flags) for 32 bit arch:
        \begin{verbatim}
cd postgresql-9.4.1
CFLAGS=-m32 CPPFLAGS=-m32 CXXFLAGS=-m32 ./configure --build=i386-apple-darwin --host=i386-apple-darwin --target=i386-apple-darwin
make\end{verbatim}
            Then you can compile \texttt{pg\_idl} and link it with \texttt{libpq.a}, still using 32 bit arch:
            \begin{verbatim}
cc -m32 -Ipostgresql-9.4.1/src/interfaces/libpq/ -Ipostgresql-9.4.1/src/include/ -fPIC -dynamic -I"/Applications/itt/idl70/external/include" -c -D_REENTRANT "/Volumes/HomeDirs/rpws/pg_idl/pgsql_query_util.c" -o "pgsql_query_util.o"
cc -m32 -Ipostgresql-9.4.1/src/interfaces/libpq/ -Ipostgresql-9.4.1/src/include/ -fPIC -dynamic -I"/Applications/itt/idl70/external/include" -c -D_REENTRANT "/Volumes/HomeDirs/rpws/pg_idl/pgsql_query.c" -o "pgsql_query.o"
cc -m32 -bundle -flat_namespace -undefined suppress -o "DLM/pgsql_query.so" "pgsql_query.o" "pgsql_query_util.o" postgresql-9.4.1/src/interfaces/libpq/libpq.a\end{verbatim}

            Finally I added the files generated in \texttt{DLM/} to \texttt{\$ROOT\_RPWS/pro/pg\_idl} and added a line in the bash and csh conf files to modify \texttt{IDL\_DLM\_PATH} to load the module:
            \begin{verbatim}
            export IDL_DLM_PATH="<IDL_DEFAULT_DLM>:+/$ROOT_RPWS/pro/pg_idl/"\end{verbatim}


    \section{Examining available IDL code}
        Code in the \texttt{/Volumes/HomeDirs/rpws/pro} directory spans several decades, encodings, commenting style, authors and IDL versions.\\
        There also exists an out-of-date copy on \texttt{/Volumes/KronosRaid/rpws\_data/rpws/pro} that will be discarded.
        \subsection{Creation time}
            \begin{verbatim}
for file in *.pro; do
    date -j -f '%s' `stat -f%B $file` '+%Y'
done|sort -un|tr '\n' ' '

2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013
            \end{verbatim}
        \subsection{Encoding}
            % Mac
            % 119 ASCII text
            %  68 ASCII English text
            %   7 Non-ISO extended-ASCII English text
            %   7 ASCII text, with CR line terminators
            %   7 ASCII Pascal program text
            %   4 ASCII c program text
            %   2 UTF-8 Unicode text
            %   2 Non-ISO extended-ASCII text
            %   2 ASCII text, with CR, LF line terminators
            %   2 ASCII FORTRAN program text
            %   1 UTF-8 Unicode English text
            %   1 Non-ISO extended-ASCII text, with CR line terminators
            %   1 Non-ISO extended-ASCII c program text, with CR line terminators
            %   1 Non-ISO extended-ASCII English text, with CR line terminators
            %   1 ISO-8859 c program text
            %   1 ASCII text, with CRLF, LF line terminators
            \begin{verbatim}
file -b *.pro|sort|uniq -c|sort -rn
    197 ASCII text
      9 Non-ISO extended-ASCII text
      7 ASCII text, with CR line terminators
      3 UTF-8 Unicode text
      3 Non-ISO extended-ASCII text, with CR line terminators
      2 FORTRAN program, 
      2 ASCII text, with CR, LF line terminators
      1 Pascal source, ASCII text
      1 ISO-8859 text
      1 ASCII text, with CRLF, LF line terminators\end{verbatim}
                \subsubsection{Line Ending}
                These files were probablly written on a VERY old Macintosh (all have a birth time around 2008-2009).
%               \begin{verbatim}
%grep -l $'\r' *.pro
%        coeff.pro
%        coeff_d.pro
%        coeff_o6.pro
%        coeff_spv.pro
%        coeff_spvr.pro
%        coeff_vip4.pro
%        coeff_vit4.pro
%        coeff_z3.pro
%        crt_sheet.pro
%        grille_sat.pro
%        magnetic_field.pro
%        mfl_foot.pro
%        xyz_to_rtp.pro\end{verbatim}
%coeff.pro
%coeff_spv.pro
%crt_sheet.pro
%ephemeris.pro
%grille_sat.pro
%high_res_plot.pro
%load_data_n3bi.pro
%make_index_files.pro
%make_loc_files.pro
%make_n3g_files.pro
%make_n3g_sed_files.pro
%mfl_foot.pro
%select_data_n3b.pro
%select_data_n3e_n3d.pro
%time_conv.pro

                    \begin{verbatim}
file -b $(grep -l $'\r' *.pro)|sort|uniq -c|sort -rn
      7 ASCII text, with CR line terminators
      3 Non-ISO extended-ASCII text, with CR line terminators
      2 ASCII text, with CR, LF line terminators
      1 ASCII text, with CRLF, LF line terminators\end{verbatim}
                Fix them:
                \begin{verbatim}
sed -i 's:\r:\n:g' $(grep -l $'\r' *.pro)\end{verbatim}
                Now:
                \begin{verbatim}
file -b *.pro|sort|uniq -c|sort -rn
    207 ASCII text
     12 Non-ISO extended-ASCII text
      3 UTF-8 Unicode text
      2 FORTRAN program, 
      1 Pascal source, ASCII text
      1 ISO-8859 text\end{verbatim}
                \subsubsection{Encoding}
                    The files marked as \texttt{Non-ISO extended-ASCII text} and \texttt{ISO-8859 text} are actually in \texttt{Mac OS Roman} encoding.\\ To fix them:
                    \begin{minted}{bash}
for file in *.pro; do
    if file "$file"|grep -Eq "(8859|Non-ISO)"; then
        iconv -f 'macintosh' -t 'UTF-8' "$file" > "$file.conv"
        mv "$file" "$file.original"
        mv "$file.conv" "$file"
    fi
done
\end{minted}
                Finally:
                \begin{verbatim}
file -b *.pro|sort|uniq -c
    207 ASCII text
      2 FORTRAN program, 
      1 Pascal source, ASCII text
     16 UTF-8 Unicode text\end{verbatim}
                %\texttt{time\_conv.pro} was detected as \texttt{ISO-8859} because of a \textcircled{c} character, it was replaced with \texttt{(c)}
        \subsection{Authors}
            \begin{verbatim}
cat authors.txt|tr -s '\t'|cut -f 2|sort|uniq -c|sort -rn
    134 BC
     45 PZ
     25 GB
     20 LL
     12 ??
      5 BC & LL
      3 BC & PZ
      2 EX
      1 LL | PZ
      1 EH
      1 BC & LL & PZ
      1 BC & GB\end{verbatim}
    \printbibliography
        \subsection{Comments and Documentation}
            \href{https://github.com/mgalloy/idldoc}{\texttt{IDLDOC}} was chosen to provide documentation to the Kronos library. A script, \texttt{make\_idldoc.sh}, was written to automatically generate comments in the new \texttt{rst} format. \\
            Then existing comments were merged by hand with the new format. \\
            To generate the \texttt{HTML} documentation, \href{https://github.com/mgalloy/idldoc}{\texttt{IDLDOC}} can be called with:
            \begin{verbatim}
idldoc, root=getenv('ROOT_RPWS')+'/pro', output=/Volumes/KronosRaid/rpws_doc/', $
   format_style='rst', markup_style='rst', /statistics, /use_latex, /debug, $
   overview=getenv('ROOT_RPWS')+'/pro/OVERVIEW.rst', title='Kronos IDL library', $
   subtitle='Library to retrieve and process CASSINI-RPWS-HFR data'\end{verbatim}
        \subsection{Versioning}
            All code has been moved to an \texttt{SVN} repository at address \url{https://version-lesia.obspm.fr/repos/waves/cassini/kronos/}. A small guide to \texttt{SVN} is available \href{http://typhon.obspm.fr/groups/rpws/wiki/6019e/SVN_Howto.html}{\textbf{here}}.
    \section{Retrieving \texttt{K} files}
        \texttt{getkfiles} cannot be called outside of the \texttt{kronosdp} graphical interface, and \texttt{check\_corres\_file\_directory} is buggy. Here is a simple way to mirror the Iowa repository:
%           \begin{verbatim}
%This shows which directories are not yet in corres_year_directory:
%for dir in $(wget -q -O - --http-user="casrpws" --http-passwd="uiowa" \
%    "http://cassini.physics.uiowa.edu/~wsk/cas/daspage/cas_data/" \
%    |sed -En "s:^.*<[aA][^>]*[hH][rR][eE][fF] *= *\
%    [\"\']([cs][0-9]{2,3}flight/)[\"\'][^>]*>.*</[aA]>.*$:\1:p"); do 
%    if ! grep -q $dir $ROOT_RPWS/pro/corres_year_directory; then 
%        echo $dir
%    fi
%done
%
%for dir in $(curl -s -u casrpws:uiowa \
%    "http://cassini.physics.uiowa.edu/~wsk/cas/daspage/cas_data/" \
%    |sed -En "s:^.*<[aA][^>]*[hH][rR][eE][fF] *= *\
%    [\"\']([cs][0-9]{2,3}flight/)[\"\'][^>]*>.*</[aA]>.*$:\1:p"); do 
%    if ! grep -q $dir $ROOT_RPWS/pro/corres_year_directory; then 
%        echo $dir
%    fi
%done\end{verbatim}
        \begin{verbatim}
wget -NS -r -np -nH --cut-dirs=4 \
    --accept-regex "http://cassini\.physics\.uiowa\.edu/~wsk/cas/daspage/cas_data/\
    (c|s)[0-9]{2,3}flight/(K[0-9]{7}\.[0-9]{2})?$" \
    --http-user=casrpws --http-passwd=uiowa \
    http://cassini.physics.uiowa.edu/~wsk/cas/daspage/cas_data/\end{verbatim}
        Initial Download:
        \begin{verbatim}
Total wall clock time: 18h 18m 52s
Downloaded: 78912 files, 21G in 12h 46m 6s (484 KB/s)\end{verbatim}
        Update only:
        \begin{verbatim}
Total wall clock time: 5h 20m 57s
Downloaded: 114 files, 15M in 33s (446 KB/s)\end{verbatim}

        \subsection{Disk usage}
            \begin{minted}{bash}
for file in $NAS_RPWS/*/k/K*; do
   du $file;
done | awk '{i+=$1} END {print i}'

Total size: 59200568K (59G)

du -ch $NAS_RPWS
3,7T
\end{minted}
%       \section{Populating the database}
%           \begin{verbatim}find /Volumes/KronosRaid/rpws_data/{Earth,Ico_m14,Turn_on,Venus1,Venus2,20*} \
%     -type f -mindepth 2 -exec bash upsert.sh "{}" \; > log_upsert
%     \end{verbatim}Fill holes in \texttt{kronos\_periods}:
% \begin{minted}{plpgsql}
% WITH des AS (
%     SELECT DISTINCT ON (period) period, year, day
%     FROM kronos_days
%     ORDER BY period, year DESC, day DESC
%   ), ascc AS (
%     SELECT DISTINCT ON (period) period, year, day
%     FROM kronos_days
%     ORDER BY period, year, day
%   )
% UPDATE kronos_periods AS p
% SET start_year = ascc.year, start_day = ascc.day, end_year = des.year, end_day = des.day
% FROM des, ascc
% WHERE des.period = ascc.period AND des.period = p.period AND p.start_day IS NULL;
% \end{minted}
%           Some k files are older than acquisition time!:
%           \begin{minted}{postgresql}
% SELECT year, day, hour, k, get_timestamp(year,day,hour) AS acquistion,
%     get_timestamp(year, day, hour) - k AS advance
% FROM kronos_hours
% WHERE get_timestamp(year, day, hour) > k;
% \end{minted}
%       Hours where the k file has been deleted:
%       \begin{minted}{sql}
% SELECT *, get_timestamp(year, day, hour) FROM kronos_hours WHERE k IS NULL ORDER BY year, day, hour;
% \end{minted}

% \begin{verbatim}
% 131497 hours
% 12995 hours with no k
% corresponding to 5159 days with at least 1 hour missing, 4583 days completely missing.\end{verbatim}
% \begin{minted}{postgresql}
% SELECT p.period, n.source, count(*), p.end_day - p.start_day + 1 AS n_days
% FROM kronos_periods AS p, kronos_ephem AS n, kronos_days AS d
% WHERE n.year=d.year AND n.day=d.day AND p.period=d.period
% GROUP BY p.period, n.source
% ORDER BY p.period, count DESC, source;
% \end{minted}

\section{Rebuilding the database from scratch}
    To delete the current database, run:
    \begin{minted}{bash}
psql91 -f drop_kronosdb.sql
    \end{minted}
    To recreate the tables, functions and views:
    \begin{minted}{bash}
psql91 -f kronos_db.sql
    \end{minted}
    Then insert all files currently in the local file tree:
    \begin{minted}{bash}
$ROOT_RPWS/pro/kronosdb/upsert.sh -v
    \end{minted}
    Delete the file list in the iowa file tree to reset the \texttt{newfiles.sh} script:
    \begin{minted}{bash}
rm -f $NAS_RPWS/iowa/.filelist
    \end{minted}
    Now you can run the script (no need to run wget). This will copy the K files again, and more importantly, it will keep a record of the correspondance between iowa and local path in the database.
    K files without a counterpart on the Iowa server will still be in the database thanks to the previous steps.
    \begin{minted}{bash}
$ROOT_RPWS/pro/kronosdb/newfiles.sh -v
    \end{minted}
    Finally, you shoud run the \texttt{fill\_periods} SQL function to set the interval for periods with a special name:
    \begin{minted}{bash}
psql91 -c "select fill_periods();"
    \end{minted}
\end{document}

%   select period, max(ord.year), min(ord.year) from (select p.period, d.year, d.day from kronos_periods AS p, kronos_days as d where start_year IS NULL and d.period=p.period ORDER BY period,year,day) AS ord GROUP BY ord.period;

%   select distinct on (d.period) d.period,d.year,d.day from kronos_days as d, kronos_periods as p where p.start_day is null and p.period = d.period order by period,year desc,day desc;

%   with des as (select distinct on (period) period,year,day from j order by period,year desc,day desc);

% WITH j AS (
%       SELECT d.period, d.year, d.day
%       FROM kronos_days as d, kronos_periods as p
%       WHERE p.start_day IS NULL AND p.period = d.period
%   ), des AS (
%       SELECT distinct ON (period) period, year, day
%       FROM j
%       ORDER BY period, year DESC, day DESC
%   ), ascc AS (
%       SELECT distinct ON (period) period, year, day
%       FROM j
%       ORDER BY period, year, day
%   )
% SELECT ascc.period, ascc.year AS start_year, ascc.day AS start_day, des.year AS end_year, des.day AS end_day
% FROM des, ascc
% WHERE des.period = ascc.period;

% WITH des AS (
%       SELECT distinct ON (period) period, year, day
%       FROM kronos_days
%       ORDER BY period, year DESC, day DESC
%   ), ascc AS (
%       SELECT distinct ON (period) period, year, day
%       FROM kronos_days
%       ORDER BY period, year, day
%   )
% SELECT ascc.period, p.start_year, ascc.year AS start_year2, p.start_day, ascc.day AS start_day2, p.end_year, des.year AS end_year2, p.end_day, des.day AS end_day2
% FROM des, ascc, kronos_periods as p
% WHERE des.period = ascc.period and des.period = p.period;

%       All days with at least one hour missing:
% select (get_ydh(ser)).year, (get_ydh(ser)).day from generate_series((select * from start_date), (select * from end_date), '1 day') as ser where not exists (select 1 from kronos_days where year=(get_ydh(ser)).year and day=(get_ydh(ser)).day) or not exists (select 1 from kronos_hours where year=(get_ydh(ser)).year and day=(get_ydh(ser)).day) or exists (select 1 from kronos_hours where year=(get_ydh(ser)).year and day=(get_ydh(ser)).day and k is null);

%cleanup ephems: either rewrite func or do another pass

