X-Git-Url: https://git.donarmstrong.com/?p=debbugs-presentations.git;a=blobdiff_plain;f=debbugs.Rnw;h=b9f0fe70dd47258759d90059aeeb02fb9a2fd138;hp=4131fcfc7c816a3c97865b933c5c38f6e7fae558;hb=ba9847f821b93274ad1d72a925b0423b3b4938b0;hpb=6e908a1c272c7c2565036306888cdc74a120672e

diff --git a/debbugs.Rnw b/debbugs.Rnw
index 4131fcf..b9f0fe7 100644
--- a/debbugs.Rnw
+++ b/debbugs.Rnw
@@ -54,6 +54,8 @@
 \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
@@ -173,7 +175,7 @@ print(ggplot(bug.growth,#[date > as.POSIXct(ISOdatetime(2005,1,1,0,0,0)),],
 \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`))+
@@ -186,7 +188,17 @@ print(ggplot(bug.growth[date > as.POSIXct(ISOdatetime(2014,1,1,0,0,0)),],
 @ 
 \end{frame}
 
-\begin{frame}{My entries into Cristian's game}
+\begin{frame}[fragile]{Is the bug filing rate decreasing?}
+\tiny
+<<bug_growth_rate_over_time>>=
+summary(lm(log(`bugs per day`)~date,
+           bug.growth[date > "2014-01-01 PST",]))
+@ 
+\normalsize
+Not significantly decreasing.
+\end{frame}
+
+\begin{frame}{My entries into Christian's game}
   \begin{columns}
     \column{0.5\textwidth}
   \begin{center}
@@ -273,15 +285,6 @@ print(ggplot(rc.bugs.long[date >
       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}
@@ -300,17 +303,11 @@ print(ggplot(rc.bugs.long[date >
   \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
@@ -321,23 +318,7 @@ print(ggplot(rc.bugs.long[date >
 \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}
@@ -354,29 +335,93 @@ debbugs-loadsql debinfo;
   \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}