\documentclass[11pt,t]{beamer} \useoutertheme{infolines} \usetheme{Darmstadt} \usepackage{color} \usepackage{times} \usefonttheme{structurebold} \usepackage{pgf,pgfarrows,pgfnodes,pgfautomata,pgfheaps} \usepackage{amsmath,amssymb} \usepackage{listings} \usepackage{graphicx} \graphicspath{{./}} \setbeamercovered{dynamic} \setbeamertemplate{itemize item}[ball] \setbeamertemplate{itemize subitem}[ball] \setbeamertemplate{itemize subsubitem}[ball] \date{\today} \title{Getting started with PostgreSQL} \author[Gavin Sherry]{Gavin Sherry \\ \texttt{gavin@alcove.com.au}} \institute[Alcove]{Alcove Systems Engineering} \AtBeginSection[] { \begin{frame} \frametitle{Outline} \tableofcontents[currentsection] \end{frame} } \begin{document} \begin{frame} \titlepage \end{frame} \section{SELECT `Hello World'} \begin{frame}[fragile] \frametitle{Installing PostgreSQL and start} \begin{itemize} \item Just do it through your package manager! \end{itemize} \begin{columns}[t] \tiny \begin{column}{5.5cm} \begin{block}{Fedora/RHEL/CentOS} \begin{verbatim} # yum install postgresql # service postgresql start \end{verbatim} \end{block} \end{column} \begin{column}{5.5cm} \begin{block}{Debian/etc} \begin{verbatim} # apt-get search postgresql # /etc/init.d/postgresql- start \end{verbatim} \end{block} \end{column} \end{columns} \begin{columns}[t] \tiny \begin{column}{5.5cm} \begin{block}{Windows installer} \begin{itemize} \item Just run the \texttt{.msi} \item \url{http://www.postgresql.org/download/} \end{itemize} \end{block} \end{column} \begin{column}{5.5cm} \begin{block}{Source} \begin{verbatim} # ./configure ; make ; make install # initdb /path/to/data # pg_ctl -D /path/to/data start \end{verbatim} \end{block} \end{column} \end{columns} \begin{center} It's that simple! \end{center} \end{frame} \begin{frame}[fragile] \frametitle{So, lets go} \begin{example} \tiny \begin{verbatim} $ psql postgres Welcome to psql 8.2.1, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit postgres=# \d No relations found. postgres=# create table foo (i int, t text); CREATE TABLE postgres=# insert into foo values(1, 'Hello World'); INSERT 0 1 postgres=# select * from foo; i | t ---+------------- 1 | Hello World (1 row) \end{verbatim} \end{example} \end{frame} \section{Hiccups} \begin{frame}[fragile] \frametitle{FATAL...?} \begin{block}{What's this?} psql: FATAL: IDENT authentication failed for user "gavin" \end{block} \begin{itemize} \item Some packages tighten up default security \item What to do: \begin{verbatim} su - postgres createuser gavin \end{verbatim} \item Alternatively, you could relax the authentication -- but I wont tell you how! \end{itemize} \end{frame} \begin{frame}[fragile] \frametitle{Exploring} \begin{example} \begin{verbatim} # select version(); version ------------------------------------------ PostgreSQL 8.2.1 ... # select table_catalog, table_name, table_type from information_schema.tables where table_schema = 'public'; table_catalog | table_name | table_type ---------------+------------+------------ postgres | foo | BASE TABLE (3 rows) \end{verbatim} \end{example} \end{frame} \begin{frame}[fragile] \frametitle{More exploring} \begin{example} \small \begin{verbatim} # select column_name, data_type from information_schema.columns where table_schema='public' and table_name = 'foo'; column_name | data_type -------------+----------- i | integer t | text (2 rows) \end{verbatim} \end{example} \end{frame} \section{Lets do something} \begin{frame} \frametitle{A little project} \begin{itemize} \item Say you want to develop a blog -- ``everyone's doing it'' \item You want to: \pause \begin{enumerate} \item Store articles \pause \item Relate articles to blog users \pause \item Keep statistics on blog posts \end{enumerate} \end{itemize} \end{frame} \begin{frame}[fragile] \frametitle{blogdb -- version 1} \begin{block}{SQL} \tiny \begin{verbatim} -- Create a table of users (this is a comment, btw) CREATE TABLE users (usrid int, username text, email text); \end{verbatim} \pause \begin{verbatim} -- insert a user (SQL isn't case sensitive) insert into users values(1, 'Gavin', 'gavin@alcove.com.au'); \end{verbatim} \pause \begin{verbatim} -- create the articles table CREATE TABLE articles (artid int, title text, body text, dt timestamp, usrid int); \end{verbatim} \pause \begin{verbatim} -- add a post insert into articles values(1, 'First post', 'Yay', current_timestamp, 1); \end{verbatim} \pause \begin{verbatim} -- get article count SELECT count(*) FROM articles; \end{verbatim} \end{block} \end{frame} \begin{frame} \frametitle{What's wrong with version 1?} \begin{itemize} \pause \item No data integrity \pause \item No permissions \pause \item \texttt{users} is a common table name -- what about other applications with a \texttt{users} table? \pause \end{itemize} \end{frame} \begin{frame} \frametitle{Data integrity} \begin{itemize} \item There are levels of integrity \item Referential integrity \begin{itemize} \item Enforce a relationship \end{itemize} \item Guard against duplication \begin{itemize} \item Each row must have a key \end{itemize} \item Some data must look a certain way -- like dates and times \item Some data cannot be `undefined' or \texttt{NULL} \end{itemize} \end{frame} \begin{frame}[fragile] \frametitle{blogdb -- version 2} \small \begin{block}{SQL} \begin{verbatim} CREATE TABLE users (usrid serial primary key, username text NOT NULL, email text NOT NULL, unique(username)); \end{verbatim} \pause \begin{verbatim} INSERT INTO users (username, email) values(1, 'Gavin', 'gavin@alcove.com.au'); \end{verbatim} \pause \begin{verbatim} CREATE TABLE articles (artid serial primary key, title text NOT NULL, body text NOT NULL, dt timestamp default current_timestamp, usrid int references users(usrid)); \end{verbatim} \end{block} \end{frame} \begin{frame}[fragile] \frametitle{Permissions and privileges} \begin{itemize} \item We speak of \texttt{CREATE ROLE} and \texttt{GRANT} \end{itemize} \begin{columns}[T] \begin{column}{7cm} \begin{block}{SQL} \small \begin{verbatim} CREATE ROLE blogread LOGIN; CREATE ROLE blogwrite LOGIN; ... GRANT SELECT ON articles,users TO blogread; GRANT ALL ON articles,users TO blobwrite; \end{verbatim} \end{block} \end{column} \begin{column}{4cm} \includegraphics[height=50mm]{cutecat}\par \end{column} \end{columns} \end{frame} \begin{frame}[fragile] \frametitle{Name space issues} \begin{itemize} \item This is why SQL supports ``schemas'' \end{itemize} \begin{block}{SQL} \begin{verbatim} CREATE SCHEMA blog; SET search_path = blog; CREATE ... SELECT count(*) FROM blog.articles; \end{verbatim} \end{block} \end{frame} \begin{frame} \frametitle{Further exercises} \begin{itemize} \item Data integrity for email addresses -- See \texttt{CREATE DOMAIN} \item Speed up \texttt{count(*)} in the presence of a large \texttt{articles} table -- see the advanced lecture today \end{itemize} \end{frame} \section{Some boring stuff} \begin{frame} \frametitle{More on PostgreSQL} \begin{columns}[T] \begin{column}{7cm} \begin{itemize} \item What's with the name? $\to$ \textbf{Post} (in) \textbf{gres}, with \textbf{SQL} support \begin{itemize} \item Hey, it used to be named POSTQUEL \item Then again, it used to have a hell of a lot of Lisp in it \end{itemize} \item Academic project in the 80s, one of \textbf{Michael Stonebraker's} projects at Berkeley \item BSD license -- do what you want with the source (don't sue us!) \item Now developed by a group of enthusiasts \end{itemize} \end{column} \begin{column}{4cm} \includegraphics[width=3cm]{stonebraker}\\ \includegraphics[width=4cm]{hackers} \end{column} \end{columns} \end{frame} \begin{frame} \frametitle{Buzzword compliance} \pause \begin{block}{What have we got?} \begin{itemize} \item JDBC, .NET, ODBC, PHP, Python, Ruby, \ldots \pause \item Replication \pause \item Views, \pause Indexes, \pause Schemas, \pause Foreign keys \pause \item Partitioning \pause \item Two phase commit \pause \item Transations \pause and nested transactions! \pause \item Functions/stored procedures \pause in SQL, \pause Java, \pause Ruby, \pause Python, \pause and much more \pause \item Online backups \pause \item \ldots \end{itemize} \end{block} \end{frame} \begin{frame} \frametitle{Who uses PostgreSQL?} \begin{itemize} \item Businesses, big and small \item Hobbyists and developers \item Big operators like \begin{itemize} \item Fujitsu \item Sun \item HP \item Apple \item Cisco \item AC Neilson \item Skype \item Just about every Australian government department \item Safeway (North America) \item .org, .info, .in, \ldots \item Sony \end{itemize} \item But it's even more popular with small businesses \end{itemize} \end{frame} \begin{frame} \frametitle{What else can you do?} \begin{itemize} \item Report writing: Jasper, Crystal, \ldots \item Wiki, CMS backend \item All the other OSS jazz -- forums, photo galleries, \ldots \item Bugzilla backend \item LDAP backend \item J2EE backend \item Ruby on rails, out of the box \item GIS -- 100\% standards compliant! \item Statistical analysis -- full integration of `R' \item Data warehousing \end{itemize} \end{frame} \section{Useful stuff} \begin{frame} \frametitle{Resources for learning more} \begin{itemize} \item The PostgreSQL manual - \url{http://www.postgresql.org/docs} \item Books (some free) - \url{http://www.postgresql.org/docs/books/} \item GUI console - \url{http://www.pgadmin.org} \item Mailing lists - \url{http://www.postgresql.org/} \item IRC - \texttt{irc.freenode.net}, \texttt{\#postgresql} \begin{itemize} \item My nick is \texttt{swm} \end{itemize} \item SydPUG - \url{http://pugs.postgresql.org/sydpug/} \end{itemize} \end{frame} \begin{frame} \frametitle{Stuff for the boss} \begin{itemize} \item Case studies: \url{http://www.postgresql.org/about/casestudies/} \item Featured users: \url{http://www.postgresql.org/about/users/} \item A glossy brochure: \url{http://alcove.com.au/postgresql.pdf} \end{itemize} \end{frame} \end{document}