\titlegraphic{\includegraphics[height=0.2\textheight,keepaspectratio]{figures/openlogo-crop.pdf}}
\subject{BTS}
+\setbeamercolor{postit}{fg=black,bg=yellow}
+
% State of the BTS: new features, changes and tips
%
% The bug tracking system is where all bugs that affect Debian are
\end{center}
\end{frame}
-\begin{frame}{Bug Growth Rate}
+\begin{frame}[fragile]{Bug Growth Rate}
<<bug_growth_rate,echo=FALSE>>=
print(ggplot(bug.growth[date > as.POSIXct(ISOdatetime(2014,1,1,0,0,0)),],
aes(x=date,y=`bugs per day`))+
scale_color_discrete("Measure"))
@
\end{center}
- \setbeamercolor{postit}{fg=black,bg=yellow}
- \begin{textblock}{4}(6,4)
- \begin{onlyenv}<2>
- \begin{beamercolorbox}[sep=1em,wd=5cm]{postit}
- \centering \huge Too many RC bugs!
- \end{beamercolorbox}
- \end{onlyenv}
- \end{textblock}
-
\end{frame}
\section{Debbugs Structure and Infrastructure}
\end{center}
\end{frame}
-\section{Database Ho!}
+\section{New Features}
-\subsection{Overall Database Design}
+\subsection{Database}
-\begin{frame}{Overall Database Design}
- \begin{center}
- \input{debbugs_layout_db}
- \end{center}
-\end{frame}
-
-\subsection{Perl Database Infrastucture}
+\subsubsection{Perl Database Infrastucture}
\begin{frame}{Perl Database Infrastructure}
\begin{itemize}
\item DBIx::Class
\end{frame}
-\subsection{SQL Design}
-\begin{frame}{SQL Schema}
- \includegraphics[width=\textwidth,keepaspectratio]{figures/schema.png}
- \begin{itemize}
- \item Current Debbugs SQL Schema
- \end{itemize}
-\end{frame}
-
-\subsubsection{dak SQL Design}
-\begin{frame}{dak SQL Schema}
- \includegraphics[width=\textwidth,keepaspectratio]{figures/dak_schema.png}
- \begin{itemize}
- \item Inspiration taken from dak SQL Schema where appropriate
- \end{itemize}
-\end{frame}
-
-\subsection{SQL Loading}
+\subsubsection{SQL Loading}
\begin{frame}[fragile]{SQL Loading}
% SQL loading
\begin{itemize}
\end{itemize}
\end{frame}
-\subsection{SQL Working}
-\begin{frame}[fragile]{SQL Working}
- % example SQL query
-\begin{lstlisting}[language=SQL]
-SELECT count(*) FROM bug
-WHERE last_modified > '2014-07-01'
-AND done IS NOT NULL
-AND owner IS NOT NULL;
-\end{lstlisting}
-\begin{visibleenv}<2>
-\begin{lstlisting}
- count
+\subsubsection{Fun Queries}
+
+\begin{frame}[fragile]{Messages to bugs}
+\small
+\begin{minted}{sql}
+SELECT count(*),c.addr FROM
+message_correspondent mc
+JOIN correspondent c ON mc.correspondent=c.id
+WHERE correspondent_type='from'
+GROUP BY mc.correspondent,c.addr
+ORDER BY count DESC LIMIT 5;
+\end{minted}
+\begin{verbatim}
+ count | addr
+-------+---------------------------------
+ 5123 | bubulle@debian.org
+ 4346 | joeyh@debian.org
+ 4214 | biebl@debian.org
+ 3875 | tbm@cyrius.com
+ 3632 | ftpmaster@ftp-master.debian.org
+\end{verbatim}
+\end{frame}
+
+\begin{frame}[fragile]{Single-message correspondents}
+\small
+\begin{minted}{sql}
+SELECT count (*) FROM
+(SELECT count(*),c.addr
+ FROM message_correspondent mc
+ JOIN correspondent c ON
+ mc.correspondent=c.id
+ WHERE correspondent_type='from'
+ GROUP BY mc.correspondent,
+ c.addr
+ HAVING count(*) = 1) AS foo;
+\end{minted}
+\begin{verbatim}
+ count
-------
- 521
-\end{lstlisting}
-\end{visibleenv}
+ 30215
+\end{verbatim}
+\end{frame}
+
+
+
+\begin{frame}[fragile]{More fun queries}
+\begin{minted}{sql}
+SELECT count(*),t.tag
+FROM bug b
+JOIN bug_tag bt ON b.id=bt.bug
+JOIN tag t on bt.tag=t.id
+GROUP by bt.tag,t.tag
+ORDER by count DESC LIMIT 5;
+\end{minted}
+\begin{verbatim}
+ count | tag
+--------+----------------
+ 141184 | patch
+ 42288 | upstream
+ 39466 | sid
+ 33640 | l10n
+ 25566 | fixed-upstream
+\end{verbatim}
+\end{frame}
+
+\subsection{Database status caching}
+\begin{frame}[fragile]{Status caching ``working''}
+\begin{minted}{sql}
+SELECT count(*),status
+FROM bug_status_cache bsc
+JOIN suite s ON bsc.suite=s.id
+WHERE s.suite_name='unstable'
+GROUP by status;
+\end{minted}
+\begin{verbatim}
+ count | status
+--------+--------
+ 514488 | fixed
+ 234155 | absent
+ 3923 | found
+\end{verbatim}
\end{frame}
\subsection{Work still needed for SQL}
\begin{frame}{Work Still needed for SQL}
\begin{itemize}
-\item Logfile loading (for full text searching)
-\item Status Caching (for faster page loading)
+\item Integration into CGI
\item Testing
\item Deployment
\end{itemize}