add fun queries

diff --git a/debbugs.Rnw b/debbugs.Rnw
index fc734035e0cc9df8a507783062c015fd89b9eb6d..9339991a7cbb662a9eff497ab5b00eace82c12ad 100644 (file)
--- a/debbugs.Rnw
+++ b/debbugs.Rnw
@@ -333,29 +333,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}