IntroductionWelcome| 00:04 | Hi! I'm Bill Weinman, and welcome to
PostgreSQL 9 with PHP Essential Training!
| | 00:09 | In this course, we'll explore
PostgreSQL 9, a full-featured, open-source,
| | 00:15 | object-relational database management system.
| | 00:17 | I'll explain the major features of
PostgreSQL and its usage in a PHP environment,
| | 00:22 | focusing on real-world examples to
aid you in implementing this powerful
| | 00:26 | database engine in your applications.
| | 00:29 | I'll show you the details of its
SQL usage, functions, data types,
| | 00:33 | expressions, and operators.
| | 00:35 | This course is a great introduction to
PostgreSQL 9, but some programming and
| | 00:40 | database experience will really help you
get the most out of these technologies.
| | 00:44 | It's really exciting to be able to use a
full-featured enterprise-class database
| | 00:48 | management system that still
maintains its free and open-source roots.
| | 00:52 | So let's dig in with PostgreSQL 9
with PHP Essential Training.
| | Collapse this transcript |
| Using the exercise files| 00:00 | If you have access to the exercise
files for this course, you can use them to
| | 00:03 | follow along with the lessons as I present them.
| | 00:06 | Because this course uses PHP, you
will need a web server installed on your
| | 00:10 | development machine, and for many of
the exercises you will need to copy the
| | 00:14 | exercise folders to the document
root directory of your web server.
| | 00:17 | Here in the Exercise Files folders,
you will find a folder for each of the
| | 00:21 | chapters in this course.
| | 00:24 | For many of the chapters you
will find an examples.sql file.
| | 00:29 | This file contains snippets of SQL to help you
follow along with the examples in the movies.
| | 00:35 | The rest of the folders contain
supporting files, for example, applications
| | 00:39 | and other purposes.
| | 00:40 | They will be explained as we use them.
| | 00:43 | Many of these files are available for
non-Premium members of the lynda.com
| | 00:46 | Online Training Library.
| | 00:48 | The chapter folders are
available for Premium members.
| | 00:51 | If you do not have access to the
chapter folders, you can easily follow along
| | 00:55 | and create files of your own.
| | Collapse this transcript |
| Prerequisites| 00:00 | PostgreSQL is typically pronounced as post-gres.
| | 00:03 | Some say PostgreSQL, but many consider
that unwieldy and simply say post-gres.
| | 00:10 | Like most SQL databases, Postgres does
its best to conform to ANSI SQL standard--
| | 00:16 | in this case, the latest version, SQL 2008--
| | 00:19 | but will break the standard
where necessary for capability or
| | 00:22 | compatibility reasons.
| | 00:24 | In reality, there are no fully
compliant SQL database systems, because the SQL
| | 00:29 | standard does not describe a
complete database management system.
| | 00:33 | Additionally, most mature database
systems pre-date the standard and maintain
| | 00:38 | some non-compliant syntax in order to
preserve compatibility with legacy code.
| | 00:43 | Postgres, dating back to the Ingres
project in the early 1980s, with its SQL
| | 00:48 | support beginning in the early 1990s,
has a great deal of legacy to support.
| | 00:53 | Postgres has all the features you
would expect from a major DBMS, including
| | 00:57 | triggers, views, transactions, save
points, foreign keys, full-text search,
| | 01:02 | replication, concurrency, object
inheritance, storage procedures, a rich set of
| | 01:07 | data types, multiple indexing
systems, and many, many more.
| | 01:11 | For this course, we will cover the
essential features necessary to use this
| | 01:15 | powerful system for simple common projects.
| | 01:18 | Postgres is suitable for many purposes,
from a small web site project to a major
| | 01:23 | corporate-distributed database system.
| | 01:26 | It supports very large data stores
with excellent performance, comparable to
| | 01:30 | current offerings from major
vendors like Oracle or Microsoft.
| | 01:35 | In order to get the most out of this
course, you should have some programming
| | 01:38 | experience and some knowledge of SQL.
| | 01:41 | If you need to learn the basics of SQL,
please see my course SQL Essential
| | 01:46 | Training here on the
lynda.com Online Training Library.
| | 01:49 | You'll need the have Postgres 9.0
or greater installed on your system.
| | 01:54 | There are movies showing how to install
Postgres on Mac, Windows, and Linux in this course.
| | 02:00 | Postgres comes with a database
administration program called pgAdmin3.
| | 02:06 | You may use the SQL Query Module in
pgAdmin to follow along with all the
| | 02:10 | exercises in this course.
| | 02:13 | In order to edit source code,
you will need a good text editor.
| | 02:16 | A text editor is
different from a word processor.
| | 02:19 | A word processor adds extra
information to text files that will make them not
| | 02:23 | work for source code.
| | 02:24 | Do not use a word processor.
| | 02:26 | For a text editor, I recommend BBEdit or
TextWrangler on a Mac, or Notepad++ on a PC.
| | 02:33 | Do not use the Notepad program
that comes with Windows, as it is not
| | 02:37 | sufficient for this purpose.
| | 02:39 | In order to demonstrate the PHP
features in this course, I'm using XAMPP,
| | 02:43 | X-A-M-P-P, a quick and simple package
installer for Apache and PHP that runs on
| | 02:50 | all modern operating systems.
| | 02:52 | Along with XAMPP, I'm using a
program called SID, which I developed for
| | 02:56 | demonstrating and testing SQL for my
SQL-related courses here on lynda.com.
| | 03:01 | You do not need to use SID in order to
follow along with the exercises in this course;
| | 03:06 | all the exercises will work fine using pgAdmin3.
| | 03:09 | I find SID easy to use, and I think
you will too, but it's not necessary.
| | 03:14 | There are movies demonstrating how to
install XAMPP and SID in this chapter.
| | Collapse this transcript |
| Installing PostgreSQL on a Mac| 00:00 | Installing Postgres on a
Mac involves a few steps.
| | 00:03 | There is a little configuring to do.
| | 00:04 | You need to create a Postgres user,
and you need to install the software.
| | 00:07 | So let's get started.
| | 00:09 | If you're running OS X Lion Server
version, Postgres is already installed.
| | 00:14 | We will be concentrating on the
non-server version of OS X 10.7 Lion.
| | 00:20 | The first thing you need to do
is to create the Postgres user.
| | 00:23 | So we are going to go into System
Preferences, and we are going to go into
| | 00:27 | Users, and we are going to create a user.
| | 00:29 | So I will click on the little lock down
here, type in my password, and now I am
| | 00:34 | going to create a user.
| | 00:36 | Now the purpose for this is that Postgres
actually requires its own user to run the server.
| | 00:41 | It's a security thing, because
Postgres is typically involved in a
| | 00:45 | server environment.
| | 00:46 | For our purposes, we are not
installing it in a server.
| | 00:48 | We are not going to be sharing this
server with other users, but we are going to
| | 00:53 | follow the procedures for
installing the software anyway.
| | 00:56 | We'll install it as a Standard user.
| | 00:58 | If you install it as a Sharing Only
user, it's not going to work, because the
| | 01:02 | standard Postgres installation actually
requires a home directory. And of course
| | 01:07 | you don't want to
install it as an Administrator,
| | 01:10 | so we are just going to
install it as a Standard user.
| | 01:13 | The Full Name will be PostgreSQL.
| | 01:17 | And the Account name is just
going to be Postgres, like this--
| | 01:21 | eight letters: p-o-s-t-g-r-e-s.
| | 01:24 | It's really important that you
install it exactly with this name, because
| | 01:28 | otherwise the installer will not work.
| | 01:30 | So I am going to give it a password.
| | 01:32 | You'll want to remember what this
password is--you're going to need it later.
| | 01:36 | And you'll probably want to
install a longer one than that.
| | 01:39 | That's just traditional for use here in
the recording booth at lynda.com. And I
| | 01:44 | am going to click on Create User.
| | 01:46 | I am also just going to be
pedantic here and change the picture.
| | 01:50 | You will notice in our Exercise Files,
under assets, there is little image here of
| | 01:56 | the Postgres elephant.
| | 01:57 | I am going to use that.
| | 02:01 | That way when I look in the list I'll
immediately see that this is the Postgres user.
| | 02:05 | And that's all we need to do.
| | 02:06 | I am going to click to lock the
changes and close the System Preferences.
| | 02:12 | Now we are going to go and download the
installer, and you'll notice this is the
| | 02:15 | page where you download.
| | 02:16 | The URL is www.postgresql.org/download/
macosx. And click on this Download for Mac
| | 02:26 | OS X and you will get this page
here, which you will notice is an
| | 02:30 | enterprisedb.com, because they do the
packaging for the installers for Postgres.
| | 02:36 | And we are going to download it from
this link right here at the top that says
| | 02:39 | Mac OS X. This is version 9.1.1-1.
| | 02:42 | Of course, by the time you do this,
it will probably be a later version.
| | 02:45 | If you're going to be following along
with this installation, you want it to be
| | 02:48 | in the 9.0 or 9.1 tree.
| | 02:51 | Now I've already downloaded it.
I have it here on my desktop.
| | 02:55 | So I am going to double-click on that.
And you will notice that the first time I
| | 02:59 | run the installer, it does
something interesting. So here we go.
| | 03:03 | I am going to double-click on the
installer, and it warns me that downloaded it
| | 03:07 | from the Internet, and I type in my password.
| | 03:12 | Instead of actually installing the
software, it gives me this message.
| | 03:16 | What it's done is it's set a file in
the etc folder to configure shared memory,
| | 03:22 | because Postgres requires a certain
amount of shared memory and OS X comes with
| | 03:27 | less shared memory than
that, installed by default.
| | 03:30 | This will not affect the performance of your
machine for any purpose other than Postgres,
| | 03:36 | so it's okay.
| | 03:37 | You go ahead and you click OK here.
| | 03:40 | If you ever want to remove this, you will simply
go to the etc folder. I will show you the file.
| | 03:44 | I am going to click OK, and we are going to go
ahead and we are going to go to the etc folder.
| | 03:47 | So I am going to click on Go.
| | 03:49 | This is in the Finder menu.
| | 03:51 | Go to Folder and type in "/etc" and
select Go, and then all the way down at the
| | 03:57 | bottom here you see this sysctl.conf.
| | 03:59 | We will go ahead and open that in our editor.
| | 04:03 | I will select BBEdit here. And you
will notice that the etc sysctl.conf
| | 04:10 | has these lines in it.
| | 04:12 | This is just about setting up shared memory.
| | 04:14 | It's not going to have any effect
on the rest of your system at all.
| | 04:17 | So we will go ahead and we will close this.
| | 04:19 | Now in order for this to take effect,
in order to be able to actually install
| | 04:23 | the software, we need to reboot the machine.
| | 04:26 | I know it's very rare for a Mac to have
to reboot in order to install something,
| | 04:29 | but this has actually changed the
system configuration, and it's just necessary.
| | 04:32 | There is no other way to do it.
| | 04:34 | So now I am going to go ahead and reboot
the machine, and I will back in a moment.
| | 04:39 | So we've rebooted the Mac, and now I am
going to go and open up this installer
| | 04:44 | again. And you will notice this time
when I double-click on it, it will open.
| | 04:50 | So now I will click on Next.
| | 04:54 | I am going to go ahead
and keep the defaults here.
| | 04:56 | So that's the directory where it will
be installed, and you will want to make a
| | 05:01 | note of that--and I will show
you where to find it in a moment.
| | 05:04 | We'll go ahead and leave that default as well.
| | 05:06 | That's where the data will be installed.
| | 05:08 | Now I am going to type in the password.
| | 05:09 | Remember, we created the Postgres user.
| | 05:11 | Now I am going to type in the
password that I entered with that user.
| | 05:15 | So you'll need to
remember what that password was.
| | 05:17 | If you don't remember what the
password was then you'll need to go right now
| | 05:20 | back to system settings, delete the
user, and create it again, and remember
| | 05:24 | what the password is.
| | 05:25 | So I am going to go ahead and type it in,
and retype it for confirmation, and I am
| | 05:31 | going to click on Next.
| | 05:32 | Now the default port for Postgres is 5432.
| | 05:36 | If this number is different here, if
the default does not say 5432, you'll
| | 05:42 | need to quit and find out what's
using the port and remove it and then come
| | 05:47 | back and install again, because we're
actually going to count on that port
| | 05:52 | number being 5432 when we install
PHP; the default PHP drivers look for
| | 05:57 | Postgres on that default port.
| | 05:59 | So I am going to go ahead and say
Next, and I am going to accept the Default
| | 06:03 | locale and say Next again.
| | 06:07 | Now it's installing a bazillion things
that are all part of the Postgres package.
| | 06:12 | Now that's installed.
| | 06:14 | I am going to uncheck this Stack Builder.
| | 06:16 | If you want to run it and you want to
look at the other things you can download
| | 06:20 | and install, go ahead and look at it.
| | 06:22 | There's nothing there that we
need for this course though.
| | 06:23 | And I am going to say Finish.
| | 06:26 | Now Postgres is installed, and it's running.
| | 06:29 | So I am going to unmount this
installer disk here, and you'll notice that
| | 06:33 | it's not down in the tray.
| | 06:34 | So I am going to come into Finder, and
I'm going to go to applications, and we are
| | 06:40 | going to find here the Postgres 9.1
folder, and in the folder you will notice
| | 06:47 | there is this pgAdmin III app.
| | 06:50 | Now there is a little bit of an issue
with this, and that issue is the icon.
| | 06:55 | The problem with this icon is
that it's mostly transparent,
| | 06:58 | so when I put it down here in my tray
you will notice that you can hardly see it.
| | 07:02 | It looks like a ghost, and
that may be okay for you.
| | 07:06 | For my purposes I found it
really hard to work with,
| | 07:09 | so I came up with a
workaround, and this is what it is.
| | 07:11 | I am just going to right-click on it
and go to Get Info, and you will notice
| | 07:15 | that there is the icon.
| | 07:16 | When I select that, I'm actually
able to paste another image in there.
| | 07:20 | So I am going to come back up here at
the Exercise Files, and under assets I've
| | 07:23 | got the official Postgres logo here.
| | 07:26 | When I double-click on that it comes
up in the preview app, and I just press
| | 07:31 | Command+A and a Command+C and that puts
it into my copy buffer, and I come over
| | 07:36 | here and Command+V to paste it.
| | 07:39 | I have to type in my password,
and it updates the icon there.
| | 07:43 | So now when I go back to Applications,
you'll notice that it's updated here, and I
| | 07:52 | put it in my tray and it's updated in my tray.
| | 07:55 | Unfortunately, when you run this, it
actually goes off and runs another program.
| | 08:00 | So when I press Command+Tab you'll
see it's got the ghost logo there again.
| | 08:04 | There's a way to fix that as well, and
I am just going to show you how to do
| | 08:08 | that, because maybe it annoys
you as much as it annoys me--
| | 08:11 | maybe it doesn't. I am going to go
to Folder, and I am going to type in
| | 08:16 | /library and scroll down to PostgreSQL 9.1
and pgAdmin III app, and just do the same thing.
| | 08:25 | Get Info, select it, press Command+V,
because it's still in my copy buffer, and
| | 08:32 | that updates it. And that's the actual
application that gets called by the other one.
| | 08:38 | So now when I click on this it brings up
the actual application and when I click
| | 08:44 | Command+Tab, I've got a real icon there.
| | 08:46 | Although it matters to me,
it may not matter to you.
| | 08:50 | So Postgres is installed.
| | 08:51 | This is pgAdmin III, which is the
application used to administer Postgres.
| | 08:57 | It's actually written by a third party.
| | 08:58 | There is a command-line client that
comes with Postgres, and we're just not going
| | 09:02 | to be using that because, well, we've
got this, and it works so much better.
| | 09:07 | So I am going to double-click on this
server, which is our local server, and that
| | 09:12 | connects it. And again, it wants
that password for the Postgres user,
| | 09:16 | so I am going to type that in.
| | 09:17 | If I want to, I can click Store password
so I don't have to type it in anymore,
| | 09:21 | and it will give me a little warning about it.
| | 09:24 | We will say Do not show that again.
| | 09:27 | Now every time I start this, I can just
double-click on the server and it will
| | 09:30 | connect to the server.
| | 09:31 | First thing we are going to do here is
we are going to create a Login Role, and
| | 09:36 | I am going to show you the best way to do this.
| | 09:38 | The easy way to do it is just click on
Login Roles and click on this New button
| | 09:42 | and create a role, and you can
certainly do that and that will work.
| | 09:46 | But when you're using Postgres in a
production environment, you're going to want
| | 09:49 | to have several different users, and
some of these users are going to have
| | 09:52 | similar sets of privileges and
are going to use common objects.
| | 09:56 | One of the advantages of the Postgres
database management system is that it's
| | 10:00 | object oriented and objects can
inherit properties from other objects.
| | 10:04 | We are going to go ahead and create a
Group Role, and then we are going to
| | 10:07 | create a Login Role that actually
inherits the permissions from the Group Role,
| | 10:11 | so you can see how that works.
| | 10:12 | So I am going to select Group Roles, and
this icon here will create a new one of
| | 10:17 | whatever you have selected down here.
| | 10:18 | So if I have Databases selected,
it'll create a new database.
| | 10:21 | If I have Group Roles selected,
it'll create a new Group Role.
| | 10:25 | So we are going to go ahead and create a
Group Role, and we are just going to call it web.
| | 10:28 | It's going to have just the default
privileges and no Role membership, and it's
| | 10:34 | just going to be called web.
| | 10:35 | You will notice that because it's a Group
Role it does not have a place for a password.
| | 10:40 | So you don't need to enter password.
| | 10:41 | All you really need to do is enter
that name. And now we are going to go and
| | 10:44 | create a Login Role that will
inherit from that Group Role.
| | 10:47 | So I can select Login Roles, press the
New button, and under Role membership,
| | 10:52 | I am going to select web and press
the little right arrow, and that'll make
| | 10:56 | this new role a member of web.
And then I am going to give it a name, and the
| | 11:00 | name will be sid, which is the name of the
application we are going to be using it with.
| | 11:06 | SID will simply inherit its
permissions from web. And under Definition we are
| | 11:12 | going to put in a password for sid.
| | 11:14 | Again, you'll need to remember this
password. And I am going to say OK, and it
| | 11:22 | creates that Login Role.
And you'll notice there are two.
| | 11:24 | There is Postgres, which is
our system one and there's sid.
| | 11:28 | Never use Postgres for applications. Bad idea.
| | 11:32 | Now under Databases we are
going to create a few databases.
| | 11:35 | We are going to create one called test,
and its owner will be web, and that means
| | 11:41 | that sid can use it.
That's pretty cool, huh?
| | 11:43 | I am going to create another one
called album, and these are going to be some of the
| | 11:49 | databases we are going to
be using for the examples.
| | 11:53 | And one more, and this one is called world.
| | 12:01 | Now that we have these databases, we
are going to go and populate them with
| | 12:03 | a little bit of data.
| | 12:04 | We are almost done here.
| | 12:06 | Under test you'll notice down here
you get the SQL that was used for
| | 12:10 | creating these objects.
| | 12:12 | I am going to select test and click on
this SQL button, and we are simply going
| | 12:17 | to open an SQL file.
| | 12:19 | So I am going to click on this Open icon
there and navigate to my Exercise Files,
| | 12:24 | and in my Exercise Files, under SQL,
you notice I have the test database,
| | 12:31 | so I am going to get the test-pg.sql file.
| | 12:35 | I am going to open that up, and in here
you see there's all of the databases
| | 12:41 | and views and things that we are
going to use in the test database.
| | 12:45 | So I go ahead and run it, and that's
this little green right arrow there.
| | 12:48 | And that's been run.
| | 12:50 | So I'll close this, and I am
going to do the same thing for album.
| | 12:53 | We will open up the album SQL file, and
we'll run it and that created the tables
| | 13:01 | and such for album, and we
will do the same thing for world.
| | 13:05 | And world is a little bit bigger
so it takes just a moment longer, and
| | 13:11 | Run, query is running. There it is.
| | 13:14 | Now it's done.
| | 13:16 | Now you will notice, if we just go ahead
and open a query window again for world
| | 13:21 | and we type in select*from view_tables--
| | 13:26 | view_tables is a view that we actually
created in that SQL file and I click Run--
| | 13:32 | there we have some results.
| | 13:33 | We have three tables--
| | 13:35 | city, country, countrylanguage--
and they're all owned by web.
| | 13:39 | I do not want to save that.
| | 13:43 | So in fact, at this point you can
actually run most of the exercises in the course.
| | 13:48 | In another movie in this chapter we
will be installing a test environment that
| | 13:51 | includes a web server and PHP, and you
will certainly need to do that if you want
| | 13:55 | to be learning about how
to use Postgres with PHP.
| | 13:59 | If you're just interested in learning
to use the database and you don't care
| | 14:01 | about the sid application, which is
just a convenient way of running the SQL
| | 14:06 | examples in the course, then you can simply
use the SQL Query application from right here.
| | 14:12 | You can select the test database, you
can press the SQL button, and you can type
| | 14:16 | in your queries here, press Go,
and see your results down here.
| | 14:21 | If you're interested in SQL or if you'd
simply like to run the sid application,
| | 14:25 | which is a more convenient way
of doing exactly the same thing,
| | 14:28 | then go ahead and watch the other
movie and install the XAMPP package, which
| | 14:31 | includes Apache and PHP, and we'll also
install the sid application with that.
| | 14:37 | But for now, Postgres is installed and
the pgAdmin III application is installed,
| | 14:42 | which allows you to administer and
use the Postgres database on your Mac.
| | Collapse this transcript |
| Installing PostgreSQL on a PC| 00:00 | In this movie, I'll be installing
Postgres 9.1 on this machine running Windows 7.
| | 00:06 | I've downloaded Postgres already from
this web site, but here's how you do it.
| | 00:10 | You go to www.postgresql.org/download, and
you'll notice a link down here for Windows.
| | 00:17 | You click on that and you'll click
on Download the one click installer.
| | 00:24 | Now this will take you to
another web site run by EnterpriseDB.
| | 00:27 | These are the folks that package up
the one-click installers for Postgres.
| | 00:33 | And you'll select the
appropriate one of these Windows versions.
| | 00:36 | I have selected the Widows x86-64, and
that's right here on my Desktop, and I'm
| | 00:42 | going to install it from there.
| | 00:45 | Now before you install Postgres, it's
really important that you turn off any
| | 00:50 | antivirus software that might be running.
| | 00:53 | I have actually experienced installing
Postgres with antivirus software running
| | 00:58 | and having that installation not work.
| | 01:02 | So you need to turn off your antivirus
software before you run this installation.
| | 01:07 | Also, you need to run the installer in
administrator mode, and that means that
| | 01:11 | you right-click on it and you
select Run as administrator.
| | 01:16 | And then the rest of it is actually pretty easy.
| | 01:18 | The installer comes up.
| | 01:20 | Now this takes a while.
| | 01:21 | You might see some dialog boxes
about Visual C and stuff like that.
| | 01:26 | Just be patient and at some point the
installer will start up. And there it is.
| | 01:33 | So I'll click on Next, and I'm going
to just accept the defaults here, and I
| | 01:38 | suggest that you do that as well. So it's
going to install it in Program Files. Click Next.
| | 01:43 | It's going to install its data in a
subdirectory of its installation directory,
| | 01:47 | and I'm going to click Next.
| | 01:49 | And now it's asking for a password for
what it calls the database superuser,
| | 01:53 | which is called Postgres, with one s,
and a service account, also called Postgres
| | 01:59 | with one s. And if these already exist
in Windows then the password has to be
| | 02:03 | the same as what you installed them with,
or in most cases those accounts will
| | 02:08 | not already exist in your Windows if
this is you're first time installing
| | 02:11 | Postgres, in which case you choose a
password and the account will be created.
| | 02:14 | So I'm just going to choose a password here.
| | 02:18 | Now you'll need to make a note of the
password that you choose here because
| | 02:21 | we're going to need it later.
| | 02:23 | So make a note of that and then press Next.
| | 02:26 | Now it's asking for the port number
and it will suggest a port number.
| | 02:29 | And again, this is really important:
| | 02:31 | this port number must be 5432.
| | 02:34 | If there's a different port number
showing in this dialog box, that means that
| | 02:38 | you probably already have Postgres
installed on your machine and you'll need to
| | 02:42 | either use that installation, or you
will need to uninstall it, or you'll need
| | 02:46 | to figure out how it's installed and use it.
| | 02:49 | So this must say 5432.
| | 02:52 | If it says something different, some
of the components in our installation
| | 02:55 | will not work properly.
| | 02:57 | Of course, it's possible to install it
on different ports, and it's complicated
| | 03:00 | to do so. And for our purposes, we
just need to get it installed so we can
| | 03:03 | start learning about it.
| | 03:05 | So if this says something besides 5432,
you need to stop the installation and
| | 03:09 | figure out what's going on and deal with that.
| | 03:11 | So I'm going to press Next.
| | 03:13 | I'm going to accept the
Default locale. Press Next.
| | 03:17 | And now when I press Next, the
installation will begin and thousands of things
| | 03:22 | will spin by on the screen as the
software gets installed. All right!
| | 03:27 | Now Postgres is installed.
| | 03:29 | You'll notice this little check box
here for Launch Stack Builder at exit.
| | 03:33 | I'm going to uncheck that. Stack Builder is
not something that we need for this course.
| | 03:38 | So I'll click Finish, and now
Postgres is installed and running.
| | 03:43 | So I'm going to click on the Start
menu here and click on All Programs and
| | 03:47 | PostgreSQL 9.1, and you'll notice
something here called pgAdmin III.
| | 03:53 | That is the Postgres administration
console that comes with the one-click installer.
| | 03:57 | This is actually built by the folks over
at EnterpriseDB and given away for free,
| | 04:02 | so I'm going to right-click on that and
click Pin to Start menu and then come up
| | 04:08 | here to the Start menu and
click on pgAdmin III and run it.
| | 04:13 | I am going to go ahead and make this
take up a little bit more of the screen
| | 04:17 | here so that we can use it properly.
| | 04:19 | And you will notice here in the left-
hand pane there is a server with an X on it
| | 04:24 | that says Postgres 9.1 on localhost 5432.
| | 04:25 | And that's our server that we just
installed, and the fact that that's there
| | 04:31 | means that it's running.
| | 04:32 | So I'm just going to double-click on
it and it goes ahead and connects, and
| | 04:36 | you'll notice that it's asking me for the
password for the user Postgres on the server.
| | 04:41 | And we made a note of that password, so
I'm going to type in that password here.
| | 04:45 | I'm going to click Store password.
| | 04:47 | This is because this is not a
production server; this is a test server and a
| | 04:51 | test server needs to be convenient.
| | 04:53 | Obviously, for a production server
you probably don't want to store the
| | 04:56 | password or you'll want to follow
whatever security procedures you have in place.
| | 05:00 | So I'm going to go ahead and click
Store password, and you may get a warning
| | 05:04 | about saving the passwords, and you can
either click the Don't want me again or
| | 05:08 | deal with that however you like.
| | 05:09 | So we have the running server here, and
we need to create a user, and we need to
| | 05:14 | create some test databases.
| | 05:17 | So we'll go ahead and do that.
| | 05:18 | Now I want to show you a feature here
that we don't really need for our test
| | 05:22 | purposes, but it's something that you
should know about, and it's called Group Roles.
| | 05:26 | So users in Postgres are called roles,
and so we could just create a role that
| | 05:30 | we're going to use with very few
permissions and have all of our databases owned
| | 05:34 | by the role and that would work just fine.
| | 05:37 | But because Postgres is an object-
oriented database and objects can inherit
| | 05:41 | other objects, I want to show you how
you can create a Group Role and have your
| | 05:46 | other roles inherit that role.
| | 05:48 | This is really useful in a production
environment, and so you should know how to do this.
| | 05:52 | So we'll start up by creating a Group
Role. So I select Group Roles here on the
| | 05:56 | left and I press this really
convenient "create a new one of these" button.
| | 06:00 | When I have Group Roles selected,
it will create a new Group Role.
| | 06:04 | If I have something else selected, like
Databases and I press that button, it
| | 06:07 | will create a new database.
| | 06:09 | So this is kind of a cool thing,
and again, it has to do with Postgres'
| | 06:14 | object-oriented nature
that allows it to do this.
| | 06:17 | So I select Group Roles and I press this
new one of these and I just name the role web.
| | 06:23 | And if I click through all of these
different tabs here, you don't need a
| | 06:27 | password, because this is not a login role.
| | 06:30 | Privileges, it's just going to
have the minimum amount of privileges.
| | 06:33 | Role membership, there
aren't any. Variables, SQL.
| | 06:36 | This is actually the SQL that
will be executed to create this role.
| | 06:41 | So I just press OK and now we
have this Group Role called web.
| | 06:45 | Now when I create a login role--and
again, I'll press the handy-dandy "create
| | 06:49 | one of these" buttons.
| | 06:51 | I'm going to name this one sid, because
that's the name of our application that
| | 06:53 | we're going to be running.
| | 06:54 | Under Definition, I'm
going to give it a password.
| | 06:57 | Now the password that I'm using I'm
just going to tell you because the PHP
| | 07:01 | script called SID and also the PHP
script called CRUD already use this password.
| | 07:05 | And if you use the same password,
then you don't need to change it.
| | 07:08 | Of course, feel free to use whatever
password you want to, but this one is
| | 07:11 | foo.bar, and I'll type it again, f-o-o-.-b-a-r.
| | 07:17 | And under Role membership, you'll
notice there is this Group Role here, so I'm
| | 07:22 | going to select that and press this
right arrow button, and that will make this
| | 07:26 | sid a member of web.
| | 07:28 | And that means the databases and other
objects that are owned by web can be used
| | 07:33 | by sid, because sid
inherits web. Pretty cool, huh?
| | 07:37 | The SQL to do this looks like this.
And when I press OK, it will actually
| | 07:42 | execute that SQL, and it will create
this role called sid, which inherits web.
| | 07:48 | Now when we create databases, we can
have them owned by web and sid can use them,
| | 07:52 | so let's go ahead and do that.
| | 07:53 | I'm going to create a database
called test, and its owner will be web.
| | 07:59 | And you'll notice the SQL looks like
that for creating the database. And I'm just
| | 08:03 | going to press OK. And this takes a moment.
| | 08:07 | And I'm going to do this a couple more times.
| | 08:09 | I'm going to create one called album, and
it's also owned by web. And I'm going to
| | 08:17 | create one called world, and
that is also owned by web.
| | 08:22 | Now we're going to go ahead
and populate these databases.
| | 08:27 | I've already copied the exercise files
to the Desktop, and you'll need that for
| | 08:32 | populating these databases.
| | 08:33 | So I'm going to click on test, and I'm
going to click on this button that says
| | 08:37 | SQL for executing SQL queries.
| | 08:41 | And we're going to open a file and in
the Exercise Files off of my Desktop, so
| | 08:46 | under Desktop and Exercise Files,
there is a folder called SQL.
| | 08:51 | And in that SQL folder, you'll see these
three SQL files. So I'm going to select
| | 08:56 | the test one and say Open,
| | 08:58 | and that opens up this SQL file where it
defines these tables and populates them.
| | 09:03 | So I'm just going to press this
green Execute query button here and that
| | 09:08 | populates that database.
| | 09:11 | I'll close this, and we'll do the same
thing under album, run the SQL query, open
| | 09:18 | the file, click on
album-pg.sql, and click Execute.
| | 09:26 | And we're going to do the same thing
one more time for world: SQL, open file,
| | 09:31 | world-pg.sql, and click on
the Execute query button.
| | 09:37 | This one takes just a moment longer.
| | 09:40 | So now we've created these three test databases.
| | 09:43 | They're owned by web.
| | 09:44 | We have our login role called sid
that inherits the permissions for web.
| | 09:47 | And we have these populated databases.
| | 09:51 | So for example, if I were to click
on test here and open the SQL query
| | 09:56 | application, I can type a query.
| | 09:58 | I can say select * from sale;
| | 10:04 | and press the Execute button,
and we get the data from the sale table.
| | 10:09 | Now in fact, you can actually follow
along with most of the exercises in this
| | 10:14 | course using this SQL query application.
| | 10:18 | Unless you're actually here to learn
also about how to use Postgres from PHP, or
| | 10:24 | if you just like to run the slick
little sid application that I've written in
| | 10:29 | PHP, you don't need to
install the XAMPP web server.
| | 10:32 | You don't need to install PHP.
| | 10:34 | You can stop right here and go on to
the rest of the course and actually learn
| | 10:40 | how to use Postgres from here.
| | 10:43 | On the other hand, if you want to learn
about using Postgres from PHP, or if you
| | 10:47 | want to run the sid application, or if
you just want your screens to look the
| | 10:50 | same as mine as we proceed through the
course, then you'll want to go ahead and
| | 10:54 | install the XAMPP package later in this chapter.
| | 10:56 | So I'm going to close this and say no,
I don't want to save those changes,
| | 11:02 | and there we have it.
| | 11:04 | In this movie, we've installed
Postgres on a Windows 7 machine.
| | 11:08 | If you're also learning how to use
Postgres with PHP or just want to follow
| | 11:11 | along with the exercises in this
course, we'll be installing XAMPP in
| | 11:15 | another movie.
| | Collapse this transcript |
| Installing PostgreSQL on Ubuntu Linux| 00:00 | In this movie, we're going to
install Postgres on a Linux desktop system.
| | 00:06 | Here I'm using Ubuntu 11.04.
This is the April 2001 version of Ubuntu.
| | 00:12 | Ubuntu is released twice a year,
| | 00:14 | so your version will probably be later,
since I am recording this in October and
| | 00:18 | they will probably release a new version soon.
| | 00:21 | If you are using a different
distribution of Linux, the process should be
| | 00:26 | roughly the same, but of course each
distribution kind of does their own thing,
| | 00:30 | so you may need to adjust.
| | 00:31 | I'll be installing Postgres 9.1.
The version released with Ubuntu 11.04 is
| | 00:39 | Postgres 8.4, which is a couple of years
old, but either way, it's probably best
| | 00:44 | to just download it and install it
from the web site than to install it from
| | 00:49 | Ubuntu's repository.
| | 00:50 | So we will start by bringing up our
web browser, and here I'm using Firefox.
| | 00:55 | I am going to go to the Postgres
homepage, which is at Postgresql.org. And I will
| | 01:03 | click on Downloads, and I'll click
on Linux, and I'll click on Graphical
| | 01:09 | Installer, and then I'll click on
Download packages from EnterpriseDB.
| | 01:14 | Now the Graphical Installers are
maintained by this other company called
| | 01:18 | EnterpriseDB, and you'll notice there
is a place to download for all these
| | 01:22 | different operating systems.
We are going to download the Linux x86-64.
| | 01:27 | That's the 64-bit Linux version.
And you see this is version 9.1.1-1.
| | 01:32 | Again, your version will probably be
later, unless you're watching this soon
| | 01:35 | after this course is released.
| | 01:37 | Now I've already downloaded it, so I
am going to go ahead and close this, and
| | 01:42 | here's the installer there on my desktop.
| | 01:44 | Now this installer needs to be run
as root, so I am going to bring up my
| | 01:48 | terminal here and I am going to
change directories to my desktop.
| | 01:52 | So that's cd Desktop. And if I type ls -l,
you will see that here is the installer.
| | 02:00 | Just expand my window a little bit here.
| | 02:04 | So here is the installer
Postgresql-blah, blah, blah.bin.
| | 02:09 | And you'll notice that it's owned by me
and that its permissions are as for a
| | 02:13 | document; it's not executable.
| | 02:15 | There is no Xs there.
| | 02:16 | So the first thing I need to do is
I need to change those permissions.
| | 02:19 | So I am going to use chmod. And I am
going to give it executable permissions,
| | 02:25 | so I am going to type 755
and the name of the file.
| | 02:28 | So I type the first few letters and
press Tab, because I am using Bash and it
| | 02:32 | has got command completion. And press
Return and we have now changed those
| | 02:37 | permissions, but let's say ls -l one more time.
| | 02:40 | You see it comes up in green, and it's
got Xs now in the permissions, which
| | 02:44 | means that it is executable.
| | 02:45 | Now I need to run this as root,
| | 02:48 | so I am going to use the sudo
command to get superuser permissions.
| | 02:52 | So I say sudo ./ and type the first
few letters of the installer and press Tab,
| | 02:59 | and this will execute the installer as
if I were root with the superuser permission.
| | 03:04 | So I'll press Enter.
| | 03:05 | It asks me for my password,
and it runs the installer.
| | 03:10 | So, welcome to the PostgreSQL setup
wizard and I press the Next button. And I am
| | 03:17 | going to leave this installing in the
default directory, which is in the opt
| | 03:20 | tree, which is a great place for this.
| | 03:22 | Press Next and the data directory
will be right off of that PostgreSQL 9.1
| | 03:28 | directory, so that's fine.
| | 03:29 | I am going to press Next again.
And now it's asking me for a password for
| | 03:33 | the database superuser.
| | 03:35 | This is not the root superuser on the machine.
| | 03:38 | This is a new user called
Postgres, eight letters--
| | 03:41 | you can see here, Postgres--
| | 03:43 | that will be created by the installer,
and the purpose of that user is to
| | 03:49 | own the Postgres server process and to
be the default user for the Postgres database.
| | 03:55 | So you are going to remember
this password--that's the point.
| | 03:58 | You are going to type in a password here and
you need to write it down and remember it
| | 04:01 | because we are going to need it later.
| | 04:02 | So I am going to type in a password here.
| | 04:04 | You probably want to use a longer one,
but here in the recording booth this is
| | 04:09 | the password we tend to use
because we tend to share it.
| | 04:12 | And I press Next here. And now it is
asking me for the port number for the server.
| | 04:17 | You will notice it says 5432.
| | 04:19 | If it does not say 5432 then you
probably have another instance of Postgres
| | 04:25 | already running on your machine, and
you want to cancel this installation and
| | 04:29 | find out about that other
installation and see if you can use that.
| | 04:32 | But the default port number for
Postgres is 5432, and so that's what should
| | 04:37 | be the default in this box, and if
it's not, you need to care of that,
| | 04:40 | because other parts of what we are
going to be installing and using depend
| | 04:44 | upon that being 5432.
| | 04:46 | So I am going to go ahead and press Next.
| | 04:48 | We will accept the Default locale,
press Next, and I will press Next one more
| | 04:52 | time and it will go ahead and install.
| | 04:55 | This takes just a couple of minutes.
And now the server should be running.
| | 05:00 | I am going to go ahead and
uncheck this stack builder.
| | 05:03 | This is not something you really need.
| | 05:05 | If you want to run it, it's okay;
it won't hurt anything.
| | 05:07 | You can install some additional tools.
But it's not necessary for the purpose of this course.
| | 05:11 | So I am going to press Finish, and the
Postgres server is now installed and running.
| | 05:16 | So if I come up here at the Applications
menu and go into Other, you will notice
| | 05:20 | there's a number of applications here.
| | 05:22 | One of them is called pgAdminIII. And I
am just going to drag that up there to
| | 05:26 | my launch bar, and I am going to click
on the little Postgres elephant there
| | 05:32 | and run this admin.
| | 05:34 | I'll make this a little bit more
full-screen for our purposes here, and you'll
| | 05:40 | notice that it has found the Postgres
server that we installed, running on
| | 05:44 | localhost at port 5432.
| | 05:47 | So when I double-click on that, it'll
ask me for the password for the user
| | 05:51 | that it created called Postgres. And so you'll
want to remember that password, type it in there.
| | 05:56 | I am going to click on Store password,
which saves that password so I don't
| | 06:00 | need to type it in again.
| | 06:01 | You may or may not want to do that.
| | 06:03 | If you don't, that's okay;
| | 06:04 | you will just need to type the password
every time you connect to the server, and
| | 06:08 | that's fine. And certainly
in a production environment
| | 06:10 | you would never save this password.
| | 06:12 | But for our purposes, just for
demonstration and for experimentation, it's
| | 06:18 | probably perfectly okay, on your
desktop machine, to store that password.
| | 06:22 | So I am going to say OK and get this
lovely warning. I'll say Don't
| | 06:26 | show me that again, and say OK, and
now I am connected to that server.
| | 06:31 | So the server is running, and the
pgAdmin application is running just fine.
| | 06:35 | Now I am going to create a couple of users here.
| | 06:38 | Now, it would be perfectly acceptable
to just create a login user called SID or
| | 06:43 | web or something like
that, and that would be fine.
| | 06:46 | But I want to show you another
feature, because Postgres is an
| | 06:49 | object-oriented database.
| | 06:50 | It allows users to inherit from other users.
| | 06:53 | So this is actually pretty cool, and
it's something that you might use in
| | 06:56 | production in the future.
| | 06:57 | So we will go ahead and do
it now, so that you know how.
| | 07:00 | I am going to create a Group Role, and
this Group Role is going to be called web,
| | 07:04 | and then I am going to create a login
role called SID that will inherit the
| | 07:08 | Group Role. And that will allow me to
have databases that are owned by web that
| | 07:13 | are used by different users, and
it makes it really easy to do that.
| | 07:17 | So I am going to create this Group Role.
| | 07:19 | I select Group Roles and I click this
cool button here, which is basically the
| | 07:23 | "create a new object of that" button.
| | 07:26 | So if I were to select Databases, it
would create a new database. See, it
| | 07:30 | says New Database.
| | 07:31 | Click on Group Roles and press this button,
| | 07:33 | it says Create a New Group Role.
| | 07:35 | So I am going to create a New Group
Role and I am going to call it web, and
| | 07:38 | under Definition, I am not going to put in a
password, because it's not a login role at all.
| | 07:43 | Under Privileges, I am just
going to leave it blank like it is.
| | 07:46 | Role membership none, Variables none,
SQL, this is the SQL that would create
| | 07:51 | this if you were to type
it into the command line client.
| | 07:54 | But for our purposes, really all we
need is that Role name web, and I am just
| | 08:00 | going to say OK. And now I am going to
create a Login Role that inherits that.
| | 08:04 | So I select Login Roles,
I click on the New button.
| | 08:07 | It says New Login Role.
| | 08:08 | I am going to call this one sid,
because that's the name of the program that we
| | 08:11 | are going to be using it with.
| | 08:12 | And under Definition, I am
going to give it a password.
| | 08:14 | I am going to type the password.
| | 08:16 | I am going to tell you what it is,
because this is the default password in my
| | 08:20 | scripts, and it just makes it convenient,
since all we are doing is using this
| | 08:24 | for demonstration purposes.
| | 08:25 | This is probably okay, so it is
foo.bar. And if you use that password, it
| | 08:32 | will make things a little bit easier;
otherwise, just remember what password you put in--
| | 08:36 | you are going to need it later.
| | 08:37 | And again, you will notice under
Role Privileges, we are just going to
| | 08:42 | inherit from the parent.
| | 08:44 | Under Role membership,
we are going to give it a parent.
| | 08:46 | I am going to select web and press this
right arrow, and that makes a Member of
| | 08:50 | web, so it will actually inherit the role web.
| | 08:54 | And then under Variables, nothing. Under
SQL, this is the SQL that you would use
| | 08:58 | if you are going to create this
role in the command line client.
| | 09:02 | But we're just going to click OK at this point.
| | 09:04 | It doesn't need any more privileges
but to inherit from web. And now we have a
| | 09:08 | Login Role called sid that
inherits from the Group Role called web--
| | 09:13 | See, it says GRANT web To sid--and
there is also a Login Role called
| | 09:17 | Postgres--that's our superuser.
| | 09:18 | We are not going to
actually use that one for anything.
| | 09:21 | And then we come over here to Databases
and create some databases that are going
| | 09:24 | to be owned by web, but sid can
use them because sid inherits web.
| | 09:28 | See, that's pretty cool!
| | 09:29 | So I'll click on this new button and
create a new database and this one will be
| | 09:33 | called test, and its owner will be web.
| | 09:37 | And again, I don't need any of this
other stuff--this is the SQL that will be
| | 09:40 | used to create the database--and I just press OK.
| | 09:44 | And I'll do another one, and this one will
be called album, and it's also owned by web.
| | 09:50 | And one more. This one is called
world, and it's also owned by web.
| | 09:57 | Now let's populate those databases.
| | 09:59 | So I am going to select the database
called test, and I am going to come up here
| | 10:02 | and I am going to press the SQL button,
and this brings up this nice little
| | 10:07 | query application. And I am going to
open a file with some queries in it.
| | 10:12 | So on my desktop I've got the
ExerciseFiles, and inside the ExerciseFiles for
| | 10:16 | this course you'll see a folder
called SQL. And I'm just going to select
| | 10:20 | the one that says test-pg.sql, and that is the
SQL for creating the tables in our test database.
| | 10:29 | So it has got all this
cool SQL in it right there.
| | 10:32 | So I just open that up and press Run,
which is this green button right there, and
| | 10:38 | you will notice that it creates
the tables and it works just fine.
| | 10:41 | So I close that and I select the album
database and I do the same thing again:
| | 10:46 | press the SQL button, open a file,
Desktop/ExerciseFiles/SQL/album-pg.sql,
| | 10:55 | and I select Run and close this.
And one more time for the world database:
| | 11:01 | SQL, open a file, Desktop/
ExerciseFiles/SQL>world-pg.sql, open and run.
| | 11:13 | This one takes a little bit longer,
and now it's done, so I'll close that.
| | 11:18 | So now we have created these three
databases that are owned by web, which means
| | 11:22 | that they can be used by sid.
| | 11:24 | And now you have Postgres installed.
| | 11:26 | You have databases created.
| | 11:28 | You have databases populated with data.
| | 11:30 | For example, if I were to select the
test database here and open the SQL query
| | 11:35 | application and just type in a
simple query, "select*from view_tables;"
| | 11:44 | and press the Go button
right here, see, we get a result.
| | 11:48 | We have three table names--
| | 11:51 | customer, item, sale--and the owner
name is web for all three of those.
| | 11:56 | So in fact, you could do all of the
exercises in this course that don't have
| | 12:00 | anything to do with PHP, which is most
of the course, right here from this query
| | 12:05 | application, and you wouldn't need
to install the Apache web server.
| | 12:08 | You wouldn't need to install PHP.
| | 12:10 | You would need to install the SID and
CRUD applications. And you could really
| | 12:14 | actually do most of course from right here.
| | 12:17 | Now I am going to go ahead and install
those other things, because this course
| | 12:21 | is also about how to use Postgres from
PHP and I just like the sid application
| | 12:27 | for demonstrating the SQL.
| | 12:29 | So follow along with me
if you want those things.
| | 12:31 | If you really want to follow along
with everything in this course then do
| | 12:34 | follow along. That's fine.
| | 12:36 | But I just want to let you know that
you can do most of this course from right
| | 12:39 | here without actually
installing the rest of these things.
| | 12:43 | So I am going to close this and no, I
am not going to save my changes to that.
| | 12:47 | And we are done with pgAdmin for now, so
I am going to close that. Now we are
| | 12:51 | going to go ahead and install Apache
and PHP, and we are going to do that from
| | 12:55 | the Ubuntu Software Center.
| | 12:57 | So here in the Ubuntu Software Center,
I am going to search for PHP5, so in
| | 13:01 | the search box I type "php5," and you see
it's a server-side HTML embedded meta package.
| | 13:08 | So this is a package that installs all
the things that are necessary to run PHP,
| | 13:12 | including the Apache web server.
| | 13:14 | So I am going to just click on Install
here, type in my password, and you notice
| | 13:20 | a little progress bar slowly moving
across that little space right there.
| | 13:25 | That's installed now, and that installs
and starts up the Apache server with PHP.
| | 13:31 | And now here in the search box, I am
just going to search "php5_pg.sql," and that
| | 13:38 | is the Postgres drivers for PHP5.
| | 13:40 | So I will select that and install
that, and now PHP and its drivers for
| | 13:47 | Postgres and the Apache web
server were all installed.
| | 13:50 | So I am going to close the Ubuntu
Software Center, and now back here in our
| | 13:54 | command line, I am just going to type
"clear" here, so that we can get a clear screen.
| | 13:59 | Now I need to do a couple of things so
that we can actually use the Apache web
| | 14:03 | server without having to
become root all the time.
| | 14:06 | So I am going to create a symbolic
link from the root of the web server file
| | 14:10 | system to my home directory.
| | 14:12 | So I just type "cd" by itself and that
will change to my home directory, and I'm
| | 14:17 | going to create a symbolic link to
the Apache web server document root.
| | 14:23 | So the way this works is like this.
| | 14:24 | I type "ln -s" for symbolic links, "/var
/www." That's the document root of the
| | 14:33 | Apache web server as it was just installed.
| | 14:36 | And I put in a dot or a period to refer to
my current home directory and press Return.
| | 14:42 | Now I have a symbolic link to that
document root, but that document root is
| | 14:47 | owned by the root user,
| | 14:48 | so I can't really use it yet.
| | 14:49 | So I am going to change directories
into that document root, cd www, and I am
| | 14:56 | going to change the permissions.
| | 14:58 | So first thing I am going to do is I am
going to change the group ownership of
| | 15:03 | the directory to admin, because my
user is a superuser on my desktop machine. I
| | 15:08 | am part of the admin group.
| | 15:09 | So I say sudo, because I have to do,
this is a superuser, because the directory
| | 15:13 | is owned by root, and chgrp to
change the group ownership of the folder.
| | 15:19 | Type the word admin, which is the
superuser group on this machine, and I know
| | 15:25 | that my user is part of that group, and a dot
refer to the current directory. Press Enter.
| | 15:31 | It asks for my password.
| | 15:33 | Now this may be actually different on
your machine, and I'm just going to have to
| | 15:37 | assume that if you are using Linux that
you have a little bit of understanding
| | 15:40 | of how permissions work on your
machine, because different distributions of
| | 15:44 | Linux are going to do this in a different way.
| | 15:46 | I know that this is working on this
version of Ubuntu--and it will probably work
| | 15:50 | on future versions of Ubuntu as well.
| | 15:52 | Now I am going to change the
permissions of that same directory by saying sudo
| | 15:56 | chmod, so it changes the permissions--
mod is the mode or the permissions--and
| | 16:02 | 775 and a dot. So that will make it
so that the group ownership also has
| | 16:10 | read/write permissions to this directory,
and I need that, because I am in the
| | 16:14 | group and I am not the owner.
| | 16:16 | And finally, there is a file in
this directory called index.html.
| | 16:19 | If I say ls -l, you will see that the
index.html is owned by root, and that's
| | 16:25 | not going to work, because I am not root.
| | 16:26 | So I need to do one more change of ownership.
| | 16:29 | I am just going to say sudo, chown--
that changes the ownership of the file--
| | 16:34 | billw--that's my user;
| | 16:36 | you are going to use your
own user--and index.html.
| | 16:40 | Now when I ls -l, you will see
that that file is owned by me.
| | 16:45 | And so I can edit if I need to.
| | 16:47 | So now we need to restart the Apache
web server, and the reason we need to
| | 16:52 | restart the Apache web server is
because after starting the web server, we
| | 16:56 | installed the Postgres drivers.
| | 16:57 | So that's really the reason.
| | 16:59 | It's not these permission changes;
| | 17:00 | we wouldn't need to
restart the server for those.
| | 17:02 | But because we installed a driver after
we installed and started the web server,
| | 17:06 | we need to restart the web server.
| | 17:07 | So sudo and then /etc--
| | 17:12 | that's the et cetera directory--/init.d--
that's where the initialization startup
| | 17:19 | scripts go--/Apache.
| | 17:21 | And I am just going to type the first
few letters and press the Tab key, so I
| | 17:25 | get Apache2 and then the word restart,
and that will restart the web server, and
| | 17:30 | you will see it says
restarting web server Apache2.
| | 17:33 | This error message about the server's
fully qualified domain name, that just
| | 17:36 | means we are not running a name
server, and that's fine. It's working okay.
| | 17:40 | You will notice that it says okay over
there on the right-hand side of the window.
| | 17:44 | So we have now restarted the web
server with our drivers, we have the
| | 17:48 | permissions that we want in the
document root directory for the web server, and
| | 17:52 | now we are ready to go ahead
and install our applications.
| | 17:55 | So I am going to close this terminal window.
| | 17:58 | I am going to type "exit."
| | 17:58 | Now I am going to open up my exercise
files, which are here on the Desktop.
| | 18:04 | I've copied those on the desktop
earlier. And I am going to create another
| | 18:08 | window here by pressing Ctrl+N--N like
in new--and that creates another window
| | 18:14 | where I can get to my www.
| | 18:16 | So you can see I am in my home directory,
and there is the symbolic link for the
| | 18:20 | www directory--that's the
document root for the Apache web server.
| | 18:24 | I am going to double-click on that.
| | 18:26 | There is my index.html file, and I am
going to come over here to my ExerciseFiles.
| | 18:30 | I am going to select CRUD, and hold
down the Ctrl key and select sid, so
| | 18:35 | those two are selected.
| | 18:36 | Now I am going to press Ctrl+C to copy.
| | 18:39 | See, at the bottom it says, "The 2 selected items
will be copied if you select the Paste command."
| | 18:44 | So I come over here into the www
folder and I press Ctrl+V, like Victor, for
| | 18:50 | paste, and it copies those two folders over.
| | 18:53 | Now if you entered a different password
than I did, you'll need to go and edit
| | 18:57 | the passwords in these two applications.
| | 18:59 | So first, we will open the CRUD
directory, and we will edit crud.php file.
| | 19:04 | I am right-clicking on it. I am going to say
open with text editor, and that opens the GNU text editor
| | 19:10 | g-edit. And I'm just going to scroll down here,
and you see where PGSQLPASS foo.bar,
| | 19:18 | so that's my default password that I use.
| | 19:20 | So when you get these exercise files,
that will be the password that's in them.
| | 19:23 | And if you set that password for the
sid user, you see, here is the sid user,
| | 19:28 | then you will be able to open those
databases that this application needs.
| | 19:33 | So change that if you need to and save and
close and do the same thing in the sid directory.
| | 19:40 | So I click on www, and I double-click on
sid, I open sid.php, and we will scroll
| | 19:47 | down, and there is the same thing.
| | 19:50 | There is the user, sid, and the password, foo.bar.
| | 19:54 | So change that if you need to, save it
and close it, and now we're ready to test
| | 19:59 | these applications in our browser.
| | 20:00 | So I am going to open the browser here.
| | 20:02 | Now we've installed a web
server on our local machine.
| | 20:05 | That web server has PHP support.
| | 20:07 | Now, we've installed the
drivers for PostgreSQL server
| | 20:12 | that's also installed and running
on our local machine, so all this is
| | 20:15 | running on our localhost.
| | 20:16 | So I type in localhost, and if I just press
Enter here, you'll see I get the It works.
| | 20:22 | That means that the web server is running.
| | 20:23 | That's that default
index.html file in the www directory.
| | 20:28 | And now if after localhost/
I type in CRUD, all caps--
| | 20:34 | that stands for Create, Read, Update,
Delete, those are the four basic functions
| | 20:38 | of any database application--
| | 20:41 | and I press / and CRUD again in
lowercase .php, and I am going to press my
| | 20:46 | Enter key, and there is our CRUD application.
| | 20:49 | You see it's accessing the database,
because it has got data up here.
| | 20:52 | You will notice at the bottom it
says "PostgreSQL Server version 9.1.1."
| | 20:56 | That means it is connecting successfully to
the server and getting that version number.
| | 21:01 | So PHP is working, Postgres is working,
the web server is working. And now we
| | 21:06 | come up here and we just edit our URL,
and instead of CRUD, we put in SID, all in
| | 21:11 | caps/sid in lowercase .php, and I am
pressing the Enter key. And now we are
| | 21:17 | running the SQL Interactive Demonstrator.
| | 21:20 | You see it's connecting the database fine.
| | 21:22 | There is our databases that we populated.
| | 21:25 | So for example, if I select
the test database and I say
| | 21:28 | select*from view_tables;--
| | 21:35 | that's a special view that is defined
in that database to just list the tables
| | 21:40 | that are defined in the database--
| | 21:42 | and I press the Go button, there you have it.
| | 21:44 | Those are the three tables:
| | 21:45 | customer, items, sale, owned by web.
| | 21:48 | This is exactly the same query that we
put into the pgAdminIII query application,
| | 21:55 | and you see we are
getting exactly the same result.
| | 21:57 | So now, if you followed along this far,
you have installed the Postgres server,
| | 22:02 | you've installed the Apache web server with PHP,
| | 22:05 | you have installed the Postgres drivers
for PHP, and you have installed the SID
| | 22:09 | and CRUD applications successfully.
| | 22:12 | This is everything you need to
follow along with all of the exercises in
| | 22:15 | this course.
| | Collapse this transcript |
| Installing XAMPP and SID on a Mac| 00:00 | In order to run PHP on your Mac,
you're going to need a web server, and you're
| | 00:05 | going to need the PHP system.
| | 00:07 | The easiest way to do this is
to install XAMPP on your Mac.
| | 00:11 | There is another course in the
lynda.com online library that teaches you how to
| | 00:16 | install XAMPP on your Mac, and we're
just going to go ahead and do it here
| | 00:19 | anyway, because we're also going to be
installing a few things on top of it, and
| | 00:23 | this just makes it easy.
| | 00:24 | So if you already have XAMPP, just
follow along until the point where we're
| | 00:28 | installing SID and CRUD, and then
follow those instructions. Or you can do the
| | 00:32 | entire installation, along with this movie.
| | 00:35 | This is the Apache Friends web
site, and you'll notice this is at
| | 00:38 | www.apachefriends.org/en/xampp-macosx.html.
| | 00:47 | XAMPP is spelled X-A-M-P-P, and I
pronounce it ZAMPP because I just can't think
| | 00:52 | of any other way to say it.
| | 00:54 | So this is the page where you would
download that from and if you scroll
| | 00:57 | down here a little bit, you can see
this is the link. And I have already
| | 01:01 | downloaded this, and I have it here
on my desktop, and I am just going to
| | 01:05 | double-click on that.
| | 01:08 | Installing XAMPP is very simple.
| | 01:10 | You simply take this XAMPP directory
and drag it over to the Applications
| | 01:14 | directory and you watch it copy
thousands and thousands of files.
| | 01:18 | Now that's done and I eject
that, and I am going to bring up the
| | 01:24 | Applications folder.
| | 01:29 | You see, down here at the bottom,
there is XAMPP, and there is the XAMPP
| | 01:33 | Control.app, which I am going to drag down
to my dock, and then I am going to run that
| | 01:39 | when I say Open. Notice it brings up
this little Getting Started window.
| | 01:44 | I am going to uncheck that and close that.
| | 01:47 | And all I have to do here is start
Apache; I don't need MySQL and I don't need
| | 01:51 | FTP. So I am just going to click Start.
For Apache, it's going to ask me for my
| | 01:55 | password, and then I get a green light.
| | 01:57 | If you don't get that green light for
some reason, one thing I would check is
| | 02:01 | I'd go into System Preferences, and I'd
go into Sharing, and I'd make sure that Web
| | 02:05 | Sharing is not checked.
| | 02:07 | If Web Sharing is checked then you
won't be able to run Apache and this green
| | 02:11 | light will now come up.
| | 02:12 | So when the green light is up, Apache
is running and I can close that, and I can
| | 02:17 | bring up my Firefox here and I can
type in localhost, and we get this Splash
| | 02:24 | screen for XAMPP. And if you click on
English, then you won't get the Splash
| | 02:28 | screen again in the future,
and you have all these things.
| | 02:31 | You can check your status.
| | 02:32 | You can check all of these different things.
| | 02:34 | You don't need MySQL; all you really
need here is PHP and as long as PHP says
| | 02:39 | Activated, then you're fine.
| | 02:40 | So I am going to go ahead and close Firefox.
| | 02:42 | I am pressing Command+Q. And now we're
going to install the SID and CRUD applications.
| | 02:48 | Again, this is very easy to do.
| | 02:50 | I am going to click on htdocs here, and
I am going to open up my ExerciseFiles,
| | 02:55 | and I am simply going to drag over and copy.
| | 02:57 | I am going to hold down the Option key
while I drag it, so that I get the little
| | 03:01 | plus sign that means it's copying.
| | 03:03 | I am going to copy that over.
| | 03:05 | Type in my password.
| | 03:06 | I am going to do the same
thing for the CRUD folder.
| | 03:08 | So I am copying over the SID
folder and the CRUD folder.
| | 03:13 | So now before we can bring up the SID
application and test it, we need to edit it.
| | 03:18 | So I am going to open this with BBEdit,
which is the editor that I am running on
| | 03:21 | the Mac--whatever text editor you're running.
| | 03:24 | And remember, you'll need a text
editor that is not a word processor
| | 03:27 | that's a plain text editor.
| | 03:29 | I recommend BBEdit or
TextWrangler, which is its little brother.
| | 03:33 | But whatever text editor you're running,
you'll want to open sid.php with that,
| | 03:37 | and you're going to need to
edit this password right here.
| | 03:40 | This is the password that you entered
for the SID user in Postgres, and so that's
| | 03:45 | just the password that I entered in
mine. And you'll want to type whatever
| | 03:49 | password it is that you entered in your
version right there on Line 27, and then
| | 03:54 | you'll save it, and you'll do
the same in the CRUD application.
| | 03:59 | So we'll come up here to CRUD and open
crud.php with your text editor and come
| | 04:05 | down here on Line 22 and enter the
password you entered for your SID user when
| | 04:11 | you set up your Postgres. And I
will close that and close the editor.
| | 04:16 | Now, when I bring up Firefox, I am going to
type in "localhost/SID/sid.php" and there it is.
| | 04:27 | You'll notice down here you have SID
Version 2.3.7, which is correct, and we have
| | 04:33 | Postgres Version 9.1.1, which is the
version of Postgres that I am running.
| | 04:37 | Yours may be a little bit later.
| | 04:39 | So that means we're connecting successfully
to the database and we're running PHP okay.
| | 04:44 | You'll notice the databases here.
| | 04:45 | We have three databases installed--
| | 04:47 | test, album, and world--and those are
the databases that we installed earlier.
| | 04:51 | If you get an error message that says
something about the password, then you
| | 04:54 | didn't get the password the same in the
sid.php file as when you set up your SID
| | 05:00 | user in pgAdminIII. And if you get
something about not being able to connect to
| | 05:05 | the database then that means that
Postgres is not installed properly.
| | 05:08 | If Postgres is installed properly, it's
going to be running all the time. And it
| | 05:12 | doesn't take up much resources when
you're not connecting to it, but if it's not
| | 05:16 | running, then it's not installed properly.
| | 05:18 | So I am going to go ahead in
here and select the test database.
| | 05:21 | I am just going to type
in here in the SQL field.
| | 05:24 | I am just going to type in
SELECT * FROM view_databases;
| | 05:34 | and press Go, and we should get this result--
| | 05:37 | test, album, and world--says 3 rows
returned and elapsed time. Your milliseconds
| | 05:42 | will be different than mine.
| | 05:44 | And let's just change it here and
say view_tables, and I've still got the
| | 05:50 | semicolon in there, and I say Go, and I get
a list of the tables in the test database:
| | 05:55 | customer, item, and sale.
| | 05:57 | Go ahead and select the album database,
leave that SQL the same, and press GO.
| | 06:01 | You get the tables and the album database,
album and track. And do the same for
| | 06:05 | the world database and press Go:
| | 06:08 | city, country, and country language.
| | 06:10 | Now, let's go ahead and
also test the CRUD application.
| | 06:13 | So I am going to change this URL here to
localhost/CRUD, which stands for Create,
| | 06:19 | Read, Update, and Delete--four basic
functions of any database application--and I
| | 06:24 | am going to type /crud.php.
| | 06:28 | Here's our CRUD application, and if it
does this, then that means it's working.
| | 06:33 | We have the CRUD version down here,
the PHP version, and there is data coming
| | 06:36 | from the album database.
| | 06:38 | So installing XAMPP and SID and CRUD on
your Mac is actually a pretty easy thing to do.
| | 06:44 | Just download it, drag it over to
applications, enter the passwords in those two
| | 06:48 | files, and you're off and running.
| | 06:50 | And so this is all the things that we
need to have installed on the Mac in order
| | 06:53 | to follow along with the
exercises in this course.
| | 06:56 | Before we finish, there is
one more thing we need to do.
| | 06:59 | We need to make the exercise
files available to the web server.
| | 07:03 | We do that by making a copy of the
Exercise Files folder and pasting that into
| | 07:07 | the web server's document root.
| | 07:08 | So we'll start by opening a new Finder
window, and we'll navigate to Applications
| | 07:15 | and XAMPP and the htdocs folder within
XAMPP, and then I am simply going to take
| | 07:22 | this ExerciseFiles folder.
| | 07:23 | I could Option+Drag, or I am just going
to right-click on it and select Copy, and
| | 07:30 | then right-click inside the
htdocs folder and select Paste.
| | 07:35 | Type in my password, and this will
make a copy of the exercise files that's
| | 07:40 | inside of the document root for the web server.
| | 07:44 | Now, when I bring up the browser, I will
type in localhost/ExerciseFiles like that.
| | 07:53 | It brings up these exercise files in
the browser, and that allows me to run the
| | 07:59 | PHP programs that are in the exercise files.
| | 08:01 | So I am just going to take this and
put that on the toolbar so I can select
| | 08:06 | that whenever I want.
| | 08:07 | I am going to do the same thing with SID
because we're going to use that quite a bit.
| | 08:10 | There is SID and drag that into the
toolbar. And so now whenever I need my
| | 08:17 | exercise files, it's there;
| | 08:18 | whenever I need SID, it's there. And that
makes this nice and easy and convenient.
| | 08:23 | So now we have successfully installed
XAMPP, set up the web server, and now we
| | 08:27 | can use this set of for
exercises in the rest of this course.
| | Collapse this transcript |
| Installing XAMPP and SID on a PC| 00:00 | PHP is a technology that
typically runs in a web server,
| | 00:03 | so in order to work with PHP,
you will need a web server.
| | 00:07 | Now you can do this using a web
server on the Internet somewhere, but it's
| | 00:11 | really much more convenient to use
a web server installed on your local
| | 00:14 | development machine.
| | 00:16 | For the purposes of this course,
I'll be using a package called XAMPP
| | 00:22 | to provide a PHP development
environment that includes an Apache web
| | 00:25 | server running PHP.
| | 00:27 | I've already installed Postgres on this machine--
| | 00:30 | this machine is running Windows 7--
| | 00:32 | but unfortunately, the latest versions of
XAMPP have some incompatibilities with Postgres,
| | 00:38 | so we must use XAMPP version
1.7.3 for our Postgres compatibility.
| | 00:43 | So I provided a copy of XAMPP 1.7.3 here on my
web site, and you'll find this at pgsql.bw.org.
| | 00:55 | And right here you can download the win32.
| | 00:58 | That's the Windows version of XAMPP.
| | 01:00 | There is no 64-bit version of XAMPP.
| | 01:02 | This is the win32 version of XAMPP that
I'm using in this course, version 1.7.3.
| | 01:09 | I've already downloaded this, and I've
saved it here on the Desktop, and there it
| | 01:13 | is, so I'm going to close the browser.
| | 01:14 | Now there's one more thing we
need to do before we install XAMPP:
| | 01:17 | we need to make sure the User
Account Control is disabled.
| | 01:21 | Like most server software, XAMPP
will not run properly with Windows User
| | 01:25 | Account Control enabled.
| | 01:27 | So we'll come down here to the Start
menu, and I'm going to open up the Control
| | 01:30 | panel, and I'm just going to, in the
Search up here, type in uac, and there it
| | 01:35 | is, Change User Account Control Settings.
| | 01:39 | So I'll click on that, and you'll
notice that it says here "Never Notify."
| | 01:43 | That means that it's turned off.
And of course Windows is complaining that
| | 01:47 | this is not recommended. That's okay.
| | 01:49 | It's a development machine, it's behind a
firewall, and it should be okay to run it this way.
| | 01:55 | If you turn on User Account Control
later, it will create problems with XAMPP,
| | 02:00 | so you need to keep this turned
off while you're running XAMPP.
| | 02:04 | So in this machine, it's already turned off.
| | 02:07 | If in your machine it's not turned off,
you're going to change this and when you
| | 02:11 | click OK, it will ask you to reboot,
and you'll have to reboot your computer
| | 02:14 | before you go ahead and install XAMPP.
| | 02:15 | So I'm just going to say OK, I'm going to
close this, and now we can install XAMPP.
| | 02:21 | I'm going to right-click on this so that I
can run the installer as an administrator.
| | 02:26 | It's also very important before you
run this installer that you disable your
| | 02:31 | virus scanner on your machine.
| | 02:33 | You can enable it again once it's
installed, but you need to disable it for the
| | 02:37 | purposes of the installation.
| | 02:39 | So I'm going to go ahead and
run this as an administrator.
| | 02:41 | I'm going to accept the default
destination here, which is the root drive on C:\--
| | 02:46 | it's actually important that it gets
installed there--and click Install, and now
| | 02:52 | it installs a bunch of files.
| | 02:54 | Now, XAMPP is a development environment.
| | 02:56 | It's not meant to be a production environment.
| | 02:59 | Do not install XAMPP on a production server.
| | 03:02 | It's got all of its security stuff turned off.
| | 03:04 | It's really just meant to be run on a
local development machine that's behind a
| | 03:09 | firewall and is not accessible on the Internet.
| | 03:13 | So here at the end of the installation
process, it's running a setup script in a
| | 03:17 | command window, and we're just
going to accept the defaults here.
| | 03:20 | Yes to adding Start menu. So I'm
pressing the Return key actually and accepting
| | 03:25 | default here for the shortcuts.
| | 03:28 | And I'm accepting the default for
the paths, and this is actually really
| | 03:32 | important--XAMPP doesn't work
well if you change the paths.
| | 03:35 | This is another one that's also important.
| | 03:37 | It's for the same reasons.
| | 03:39 | You just accept the default, which is a No here.
| | 03:41 | I'm pressing the Return key again.
And moving some things around and it's done.
| | 03:46 | I'm going to press the Return key, and
the time zone, you can change this later
| | 03:51 | in php.ini and my.ini.
| | 03:54 | So I'm just pressing the Return key, and
here I'm going to type the letter X and
| | 03:58 | press Return, and XAMPP is now installed.
| | 04:02 | So you'll notice a shortcut on the
Desktop here for the XAMPP control panel, and
| | 04:07 | I'm going to double-click on that,
and that's how we start the servers.
| | 04:11 | Now I'm not really interested in any of
these other ones except the Apache server.
| | 04:15 | But before I press Start for the Apache
server, I'm just going to leave that alone there--
| | 04:20 | if you've jumped ahead and
pressed Start, you can just press Stop;
| | 04:22 | that's fine--because we need to edit
something in the PHP configuration file in
| | 04:28 | order for it to work with Postgres.
| | 04:30 | So I'm going to go ahead here and open an
Explorer window, and I'm going to go to Local C:
| | 04:35 | disk and open the xampp directory, which
is off the root of the local C: Disk.
| | 04:41 | And in here I'm going to double-click
on php folder, and I'm going to scroll
| | 04:45 | down until I find a file called php.ini, and
I'm going to open this with my text editor.
| | 04:51 | Now I've got Notepad++ here
installed on this machine.
| | 04:55 | It's really important that you don't
use a word Processor, and also that you
| | 04:58 | don't use the Notepad file that comes
with Windows, which is notorious for
| | 05:02 | changing line endings.
| | 05:04 | And then those line endings get changed
| | 05:06 | you don't see it on the screen, but
it really messes with stuff, and it
| | 05:10 | makes things not work.
| | 05:11 | So that's why I've got this Notepad++.
| | 05:14 | You can search for Notepad++ in Google
or wherever you do your searching and
| | 05:19 | simply install it for your machine.
| | 05:21 | It's a pretty good full-featured text editor
for the kinds of purposes that we have here.
| | 05:26 | And I've opened this file before
apparently, so it wasn't at the beginning, but
| | 05:30 | I'm just going to bring it up to
the beginning so I can show you.
| | 05:32 | And I press Ctrl+F, like
Find, and I search for pgsql.
| | 05:37 | I'm going to press Enter, and I'm just
going to close this Search box because
| | 05:43 | it has now found it.
| | 05:44 | Now you'll see a line here that says
extension=php_pdo_pgsql.dll. And there's
| | 05:52 | a semicolon at the beginning of that line,
and that makes it commented out, as we say.
| | 05:57 | So I simply remove that
semicolon, and now that line is active.
| | 06:01 | And then a few lines down you'll see
another one here that just says php_pgsql,
| | 06:08 | and that one also I'm going to uncomment.
| | 06:11 | I'm going to remove that semicolon, and those
are the two lines that we need to change here.
| | 06:17 | So those lines are now
uncommented, and I'm going to click Save.
| | 06:21 | First, I'm going to close the file, or I'm
going to press the little x there to close the file.
| | 06:25 | And then I'm going to close Notepad++
and close this Explorer window, and now we
| | 06:31 | can start PHP and this will all run fine.
| | 06:34 | So I'm going to start the Apache web
server, which provides PHP, and you see the
| | 06:39 | little green Running comes up.
| | 06:41 | That's exactly what we want.
| | 06:42 | I can close this control panel now, and
I can bring up my browser, so I'm going
| | 06:47 | to bring up Mozilla Firefox.
| | 06:49 | Now if I just type in localhost, it
will connect to the Apache web server and
| | 06:55 | make sure that that's running,
| | 06:57 | so I'm just going to do that. And we
get this screen that says XAMPP, and it's
| | 07:00 | got a bunch of languages here.
| | 07:01 | If we click on English, we get the
little XAMPP Status page, and you can click on
| | 07:06 | Status, which is right under Welcome
here, and it takes just a moment while it
| | 07:10 | checks what's running and what's not running.
| | 07:12 | And you see that PHP is active,
and that's the thing that we are the
| | 07:16 | most concerned with.
| | 07:18 | PHP is working, and so that's all we need there.
| | 07:21 | So now I'm just going to close
Firefox, and we're going to go ahead and
| | 07:24 | install SID and CRUD.
| | 07:27 | So I'm going to open up the
ExerciseFiles here off of my Desktop.
| | 07:29 | These are the exercise files
that come with this course.
| | 07:32 | And in here you'll notice two
folders: one called SID and one called CRUD.
| | 07:36 | We're going to need those, but first I
need to open another Explorer window for
| | 07:40 | the destination of where we're going to copy them.
| | 07:42 | So I'm going to press Ctrl+N for a new
Explorer window and just kind of move it
| | 07:48 | over here to the side on the screen,
and I'm going to come back down here to
| | 07:51 | local disk, the xampp directory,
and under xampp, you'll find htdocs.
| | 07:57 | This is what's called the document
root directory for the Apache web server.
| | 08:02 | So I double-click on that. It's got
just a few files in it, but what I want to
| | 08:06 | do is I want to copy these SID and CRUD folders.
| | 08:09 | So I'm going to select the CRUD folder,
and I'm going to hold down the Ctrl key
| | 08:13 | while I select the SID folder,
and that will select these two.
| | 08:16 | I'm just going to press Ctrl+C to copy
and then come over and select the htdocs
| | 08:22 | folder and press Ctrl+V to paste,
and it copies over those two folders.
| | 08:29 | Now here's where you have to pay
attention to that password that you entered for
| | 08:34 | the SID role in Postgres.
| | 08:36 | When we installed Postgres, we
installed a user, or a role, called SID, and we
| | 08:43 | gave it a password,
| | 08:44 | so you'll need that password right now.
| | 08:46 | So I'm going to open the SID folder, and
I'm going to open the sid.php in my editor.
| | 08:52 | So I right-click on it and
select Edit with Notepad++.
| | 08:57 | And right down here at line 27 you see it says
PGSQLPASS, and it says foo.bar for that password.
| | 09:06 | And that's where you'll put it
in the password for your sid user.
| | 09:09 | So on the line above it, it says sid is
the user, and even though it says PGSQLUSER,
| | 09:15 | it's actually called a role in Postgres.
| | 09:17 | So that's the sid role,
and the password is foo.bar.
| | 09:21 | If you didn't change the password, if
you used the same password that I used in
| | 09:24 | the installation of Postgres, then you
probably don't even need to edit this
| | 09:28 | file, but I just want to show
you how to do it so you know how.
| | 09:31 | So I'm going to close this, and then
we're going to go and do the same thing
| | 09:36 | in the CRUD directory.
| | 09:39 | Right-click on crud.php.
| | 09:41 | There it is, on line 21 and 22.
The user is sid, and the password is foo.bar.
| | 09:48 | So once these two files are edited, we
can close this, we can close this, and we
| | 09:53 | can bring up our web browser again.
And this time we're going to type in
| | 09:57 | localhost/CRUD/ and that's all
caps, and then lowercase crud.php.
| | 10:07 | CRUD stands for Create Read Update Delete.
| | 10:10 | These are the four basic functions of
any database application, and this is
| | 10:14 | just an application written in PHP that
demonstrates these four functions in Postgres.
| | 10:19 | So I press Return and it brings up the
CRUD application, and you can see it's
| | 10:24 | reading data from the database.
Everything is working fine.
| | 10:27 | So we're going to do the same for SID.
| | 10:29 | So I'm just going to select that part
of the URL, I'm going to type in SID
| | 10:34 | in all caps, and then sid.php,
and it brings up the SID application.
| | 10:40 | This is the SQL interactive demonstrator.
| | 10:42 | This is what we'll be using to learn
about the particular SQL syntax for Postgres.
| | 10:49 | You'll notice down here it says
Postgres server version 9.1.1, so it's
| | 10:53 | successfully connecting to the server.
| | 10:56 | And we have these three test
databases listed here, so I'm just going to
| | 11:01 | select one of them.
| | 11:02 | I'm going to select test. I'm going to
type in here some SQL, SELECT * FROM sale;
| | 11:11 | and Go, and that's the same result that we
got in the Postgres SQL Query application.
| | 11:18 | So we can see it's successfully
connecting to the database, and it's successfully
| | 11:22 | sending queries and getting
results from the database.
| | 11:26 | So this means that we have
successfully installed XAMPP.
| | 11:30 | We've successfully installed
the Postgres drivers for PHP.
| | 11:35 | We've successfully installed the
application programs from the exercise files
| | 11:40 | SID and CRUD, and all of this is
working well with the Postgres server that we
| | 11:45 | installed in a different movie in this chapter.
| | 11:48 | So now we can use these tools as we
go ahead and run the exercises in the
| | 11:53 | rest of this course.
| | 11:55 | Before we finish, there is
one more thing we need to do.
| | 11:58 | We need to make the exercise files
available to the web server, and we'll do that
| | 12:02 | by making a copy of the Exercise
Files folder and pasting that into the web
| | 12:06 | server's document root.
| | 12:08 | So we'll start by bringing up an
Explorer window here. And I'm going to
| | 12:12 | select the local disk C:,
double-click on the xampp directory there and
| | 12:17 | double-click on htdocs--
| | 12:19 | that's the document root right there--and
you see that it's got the SID and CRUD folders.
| | 12:23 | And I'm going to come over here
and I'm going to just make a copy of
| | 12:26 | this ExerciseFiles.
| | 12:27 | I could Ctrl+Drag it, or I can just
right-click on it and select Copy, and then
| | 12:34 | right-click on the Explorer window
here in the web server document root and
| | 12:39 | select Paste, and that makes a
copy of that ExerciseFiles folder.
| | 12:45 | Now I'll just bring up my web server.
And when I type in localhost/ExerciseFiles
| | 12:54 | and make sure that I capitalize it
write--this is case sensitive here--and there
| | 12:58 | we see that I've got those
exercise files right there.
| | 13:01 | So I'm just going to drag this onto
my bookmark bar and I'm also going to
| | 13:06 | put SID there as well.
| | 13:08 | So I've got SID there, localhost/SID/sid.php,
and I'm going to put that also on
| | 13:14 | my bookmark bar, so I can get back to
SID when I want, and I can get back to the
| | 13:18 | exercise files when I want, and
that makes this all really easy.
| | 13:22 | So now we have successfully installed
XAMPP, and we've set up the web server, and
| | 13:29 | now we can use this setup for the
exercises in the rest of the course.
| | Collapse this transcript |
|
|
1. Quick StartGetting the most out of the Quick Start| 00:00 | This chapter is for those of you who
are familiar with another relational
| | 00:02 | database management system and who know
enough PHP and SQL to get your job done
| | 00:07 | and are looking to get up and running
with Postgres as quickly as possible.
| | 00:11 | For the purposes of this quick start, an
experienced developer is someone who has
| | 00:15 | experience using PHP or another
similar C-like scripting language, and
| | 00:20 | experience with SQL and another
SQL-based database management system.
| | 00:26 | The examples in this chapter use a
PHP sandbox script as a template.
| | 00:30 | Before you continue, it's important that
you update the password in this file to
| | 00:34 | match the password you entered when
you created the sid user in the pgAdmin.
| | 00:39 | You see here on line 14 I have foo.bar.
| | 00:42 | This is where you'll need to put the
password for your sid user exactly as you
| | 00:46 | entered it in pgAdmin.
| | 00:48 | Let's take a look at here.
| | 00:49 | This is in PHP, and we're just going
to start at the bottom for our purposes
| | 00:53 | right now. And down here at the bottom
you see there is a function for displaying
| | 00:57 | errors, there is a function for
displaying error messages, there is a function
| | 01:01 | for just displaying a message, and
there's a function for displaying content.
| | 01:05 | These functions, combined with some
CSS, make your screen look like this.
| | 01:11 | Then going back up to the top, you
see we have some variables are defined.
| | 01:16 | We bring in the bwPgSQL.php library--
and that gets described in some detail later
| | 01:22 | on in the course--and we'll call an
init function, the main function where the
| | 01:26 | work is done and a page
function to display the pages.
| | 01:30 | The init function defines some global
variables, initializes an array for the
| | 01:35 | display variables, and connects to the
database, creates a table, populates that
| | 01:41 | table, and it does all of this
within a try block so that it can handle
| | 01:45 | exceptions for errors.
| | 01:49 | Then the main function displays some
messages, starts a timer, which is then
| | 01:54 | displayed at the bottom, and has
another try block for whatever database
| | 02:00 | operations we're demonstrating
in this particular exercise.
| | 02:04 | So this is where the bulk of the
work is done here in the main function.
| | 02:08 | Then finally this page function brings
in a header, a body, and a footer PHP
| | 02:13 | pages, and fills in the variables that
display the actual content on the screen.
| | 02:17 | So this is what it looks like in the browser.
| | 02:20 | This is our start.php script, which is the same
one when we're looking at here in the editor.
| | 02:26 | And you see the little bit messages
where it connects to the database,
| | 02:30 | it populates the database with four
rows in the table, and it displays the
| | 02:34 | content there in the main function.
| | 02:37 | The examples in this chapter also
use a PHP library called bwPgSQL.
| | 02:42 | It's basically a wrapper around the
PHP functions that use Postgres and the
| | 02:48 | Postgres driver--and this library is
explained detail later in the course.
| | 02:53 | It uses PHP's excellent PDO
interface for talking to the database, and it
| | 02:58 | provides direct SQL support, as
well as simple CRUD functionality.
| | 03:02 | Feel free to use this library as a
starting place or to write your own
| | 03:06 | library from scratch.
| | 03:07 | The library provides a simple
object-oriented interface that can be a real
| | 03:12 | timesaver for you, with
very little performance cost.
| | 03:16 | It supports most common database operations.
| | 03:18 | It uses placeholder
arguments for SQL parameters.
| | 03:22 | It has a CRUD interface--that's Create
Read Update Delete--the basic four
| | 03:26 | functions of a database application.
| | 03:29 | The CRUD records are passed as
convenient associative arrays.
| | 03:33 | Rows are also returned as associative arrays.
| | 03:36 | It does not provide support for
prepared statements, although it does use
| | 03:39 | prepared statements internally for performance.
| | 03:42 | So again, this chapter is a quick start.
| | 03:44 | If you have the necessary experience
and knowledge, this should help you get up
| | 03:48 | and running with Postgres quickly and easily.
| | Collapse this transcript |
| Creating a database| 00:00 | As with most operations in Postgres,
databases are created using SQL.
| | 00:05 | While it's certainly possible to just
open up the pgSQL command line interface
| | 00:09 | and type "Create Database," this is
not generally the best way to do it.
| | 00:14 | Instead we're going to use the pgAdmin
III application to create a database.
| | 00:19 | So I'm just going to double-click here
on the Postgres9.1 server and it'll go
| | 00:25 | ahead and login to that, because we
had saved the password here before.
| | 00:28 | If you haven't saved the password,
you might need to type your password.
| | 00:30 | And I'm going to select the databases
here, and we'll see that we've got these
| | 00:34 | three databases that we've already
created plus the Postgres database, which is
| | 00:38 | required and will always be there.
| | 00:40 | You should never delete that.
| | 00:41 | It's used by the system.
| | 00:43 | So now to create a database all I do is,
with this database is selected here in
| | 00:48 | the Object browser panel, I just
click on this magic button up here, the
| | 00:53 | "create a new object of the same type" button,
and that will bring up the New Database dialog.
| | 00:58 | So I'll just type in a name for the database--
| | 01:01 | I'm going to call this one foo--
and I'll select an owner for it.
| | 01:05 | We'll just select web for the owner.
| | 01:07 | And you'll notice it's got a few tabs here.
| | 01:09 | Under Definition, we have a place for encoding.
| | 01:12 | UTF8 is of course a very
common and useful encoding.
| | 01:16 | See, there is a lot of other
encodings available there.
| | 01:18 | We're just going to leave it at UTF8.
| | 01:20 | There is a place for a template, and
you'll notice that there are a lot of names
| | 01:24 | of databases in here.
| | 01:26 | There is also this template1 and template0.
| | 01:28 | So because of the object-oriented
nature of Postgres, all databases inherit from
| | 01:33 | one of the template databases.
| | 01:36 | The template1 database is the default database.
| | 01:38 | If I just leave it at the default and
don't select a template, it'll use template1.
| | 01:42 | And template1 is there--you
can actually modify that.
| | 01:45 | You can add things to it, like one common
use for this is to modify it so that it
| | 01:50 | includes a language for stored
procedures. And you can also inherit from
| | 01:55 | template0 if you like.
| | 01:56 | template1 inherits from template0, and
template0 is basically the one that you're
| | 02:01 | never supposed to modify.
| | 02:02 | This will always be the default.
| | 02:04 | You should leave that one alone, in
case you have modified template1 and you
| | 02:08 | still want to be able to
get the default database.
| | 02:11 | So you can also use any of the other
databases that you've created and use one of
| | 02:16 | those as a template as well.
| | 02:18 | But in this case we're going to just
leave the default, and that's what you'll
| | 02:20 | probably do most of the time.
| | 02:22 | You can also select a
tablespace if you create tablespaces.
| | 02:25 | This allows you to store your data
on different volumes in different
| | 02:28 | places within your system.
| | 02:30 | That might be valuable for a
large data store or a large
| | 02:33 | production installation.
| | 02:35 | Of course, we're not going to
use anything like that here.
| | 02:37 | For our purposes, we are just
really going to accept the defaults for
| | 02:40 | most things, because all we're
doing at this point is we're learning
| | 02:43 | about the database,
| | 02:44 | we're exploring the database;
| | 02:46 | we're learning how
Postgres works. So for Collation,
| | 02:49 | you have a choice between C and POSIX, and
there may be some other choices in your
| | 02:54 | installation. Character type, likewise,
and as long as the encoding as UTF8, you're
| | 02:59 | probably just fine with the defaults.
| | 03:01 | Connection Limit of -1
just means there's no limit.
| | 03:05 | Under Variables there is a lot of
different variables that you can set, and these
| | 03:09 | are mostly be used for performance
enhancement and setting certain defaults and
| | 03:14 | options within the database.
| | 03:16 | Again, those are the kinds of things
you'd probably set in a template and then
| | 03:20 | just inherit them for your production databases.
| | 03:23 | Under Privileges you can set your
privileges with some pretty good granularity,
| | 03:27 | and you can have different privileges
for different roles. And then in the SQL
| | 03:31 | tab of course, we just tab the SQL that
will be used to create the database with
| | 03:35 | the options that you've selected
throughout the rest of the tabs.
| | 03:38 | In this case it's just very
simple create database statement.
| | 03:41 | So we go ahead and say OK,
and that creates our database.
| | 03:45 | So now we have this foo database
there, and we can go ahead and use it.
| | 03:49 | So I'm going to bring up the SQL Query
Editor, and I'm just going to type in here
| | 03:53 | CREATE TABLE foo (a TEXT, b TEXT);.
| | 04:02 | And I'll go and run that.
| | 04:03 | As you can see we get a message
that says, "Query returned successfully."
| | 04:06 | Of course, there is no result because
there's no result from a CREATE TABLE,
| | 04:10 | but it created a table. And now we can
insert some data into it, and we'll see
| | 04:15 | INSERT INTO foo VALUES. And I'll just
put a couple of rows of values in here,
| | 04:21 | ('one', 'two'), ('three', 'four'); and
I'll run that, and you'll see two rows affected.
| | 04:35 | So that means we've inserted data into
two rows of the table, and now I'll just
| | 04:39 | say SELECT * FROM foo and run that and
we get the result in our data output.
| | 04:46 | So I'm going to go ahead and drop that
table and execute that, and we can close
| | 04:54 | this and say no we're not going to
save our changes, and then we can go ahead
| | 04:59 | and we can drop that table as well.
| | 05:01 | I can select Delete/Drop from the
context menu like that, or I can just click on
| | 05:06 | this trashcan icon with the database
selected and that will go ahead and
| | 05:11 | drop the database foo.
| | 05:12 | So you can see that pgAdmin makes it
easy to create databases and to manage our
| | 05:19 | permissions, and to even
create tables and populate them.
| | 05:22 | Once you have the database created, you
can use it from whatever interface you
| | 05:26 | have designed it for.
| | Collapse this transcript |
| Creating a table| 00:00 | Creating a table in Postgres is easy;
| | 00:02 | you just use the SQL CREATE statement
to define the columns that you're using.
| | 00:07 | So let's go ahead and
create a table in a PHP script.
| | 00:11 | We're going to start with this create-
start.php file, and I'm just going to make
| | 00:15 | a working copy of it here, and
I'm going to name that create.php.
| | 00:21 | That way if I need to revert back,
it's easy to do. And I'm going to open
| | 00:25 | that in my text editor.
| | 00:27 | Before you continue, it's important that
you update the password in this file to
| | 00:31 | match the password you entered when
you created the sid user in pgAdmin.
| | 00:36 | You see here on line 14 I have foo.bar.
This is where you'll need to put the
| | 00:40 | password for your SID user
exactly as you entered it in pgAdmin.
| | 00:45 | Now this is based on the sandbox we're
using for this quick start chapter, and
| | 00:50 | the difference here is that my init
function really doesn't have anything in it;
| | 00:55 | instead, I'm going to be creating the
table here in the main function.
| | 00:59 | And inside this try block, so
that I can catch exceptions,
| | 01:03 | I'm going to go ahead and instantiate
the database object. So I'll put out a
| | 01:08 | message, creating the db object,
| | 01:13 | and I'll use a variable called db = new
bwPgSQL( PGUSER, PGPASS, DATABASE,
| | 01:28 | TABLE_NAME)--and these are all
constants that are defined up here at the top.
| | 01:34 | There they are: PGPASS, PGUSER,
DATABASE, TABLE_NAME. And I'm also going to
| | 01:40 | set a variable for the TABLE_NAME, and that
will allow us to use it in the strings below.
| | 01:49 | Now we're going to create the
table, and I'll put out a message that
| | 01:52 | we're creating the table. And we'll use
that db object, and we'll use the sql_do method.
| | 02:04 | Keep in mind that the bwPgSQL library is
explained in a lot of detail later on in the course.
| | 02:11 | Now we're going to use Postgres
special syntax for conditionally dropping a
| | 02:16 | table if it already exists,
which is DROP TABLE IF EXISTS.
| | 02:21 | A lot of database systems have that same
syntax, so it might look familiar. $tn,
| | 02:26 | that's the table name there, and
that's just a simple way of making sure that
| | 02:32 | we're starting with a fresh table each time.
| | 02:34 | Again, I'm going to do the sql_do, and
this time I'm going to CREATE TABLE $tn.
| | 02:42 | It looks like I used an h up here by
accident, so I'm going to correct that.
| | 02:47 | My command completion was not giving
me the correct variable there, and so now
| | 02:54 | I know why that was.
| | 02:56 | Now I'm going to define the columns.
| | 02:58 | The first column is id column, and it's
going to use SERIAL PRIMARY KEY as its type,
| | 03:07 | and that's a special type in
Postgres that's basically an automatically
| | 03:12 | incrementing integer.
| | 03:14 | It's great for ID columns.
| | 03:16 | And I use this because in the bwPgSQL
library, for the CRUD functions, they
| | 03:22 | depend on an id column having that property.
| | 03:26 | So this actually makes that same
library work great across different
| | 03:30 | database technologies.
| | 03:31 | I can use pretty much the same
interface for Postgres, for MySQL, for SQLite.
| | 03:38 | And then we'll have a couple of text
columns: animal and sound--the sound
| | 03:44 | that the animal makes.
| | 03:48 | So that creates a table.
| | 03:49 | It's got three columns: this
automatically incrementing column called id and an
| | 03:54 | animal column and a sound
column that are both text columns.
| | 03:57 | Now I'm going to go ahead
and insert some records.
| | 04:00 | And again, I'm going to use this
sql_do("INSERT INTO $tn, animal and sound)--
| | 04:15 | we'll use those columns--and VALUES.
And we're going to use the placeholders
| | 04:20 | here, SQL placeholders.
| | 04:23 | And that part will remain the same
for each row. And we'll have a cat.
| | 04:28 | We'll make it purr, how's that?
| | 04:32 | And now I'm just going to copy that, and
all I have to change is the animal--this
| | 04:38 | one would be a dog--and the sound, woof.
| | 04:44 | And duck, and it will say quack,
and a bear, and it will say grrr.
| | 04:55 | Now I'll put out one little message
here, and this one tell us how many rows
| | 05:02 | there are in the table.
| | 05:03 | It will use the count_recs method from
the database library, and now we're going
| | 05:19 | to drop the table just to put
it back in its original state.
| | 05:28 | See my command completion is working
properly there now, and we'll put out a
| | 05:32 | message that we've dropped the table.
| | 05:34 | I'll put it in italics.
| | 05:44 | You notice that my message function uses
variadic variables just like printf in C.
| | 05:50 | So there we have it.
| | 05:52 | Just look over it; make
sure there aren't any typos.
| | 05:54 | We instantiate a new object
from the database library,
| | 05:58 | we assign this variable tn, TABLE_NAME,
Create the table, DROP TABLE IF EXISTS.
| | 06:04 | Here's the CREATE TABLE
SQL, and that all looks right.
| | 06:07 | We insert some records using SQL placeholders--
| | 06:11 | that's a really valuable feature there--
and display a message, how many rows in
| | 06:15 | the table, and then we drop the table
and put out a message to that effect.
| | 06:19 | So I'm going to go ahead and save this--
| | 06:22 | I'm pressing Command+S to save the file--
and switch to the browser, and we'll go
| | 06:28 | ahead and load it up and see if it works.
| | 06:31 | So there's the create.php, and there it is.
| | 06:34 | So here we're connecting to the database.
| | 06:36 | We've got the database version.
| | 06:38 | We created the db object, create the table.
| | 06:40 | There are four rows in the table, and
then we dropped the table, and that all
| | 06:44 | happens in 28 milliseconds.
| | 06:46 | So there you can see, creating a table in
Postgres is just like in most SQL-based systems:
| | 06:52 | it's simply a matter of defining your
columns using SQL. And we'll cover in more
| | 06:56 | detail on the various data types that
Postgres supports later on in this course.
| | Collapse this transcript |
| Inserting data into a table| 00:00 | Inserting data into a table is
accomplished with the SQL insert statement.
| | 00:04 | Let's see how this is done in a PHP script.
| | 00:08 | We are going to make a working copy,
starting with the start.php file, and I am
| | 00:14 | going to name this "insert."
| | 00:17 | Open it in the editor.
| | 00:19 | Before you continue, it's important that
you update the password in this file to
| | 00:23 | match the password you entered when
you created the sid user in pgAdmin.
| | 00:28 | You see here on line 14 I have foo.bar.
| | 00:31 | This is where you'll need to put a
password for your sid user exactly as you
| | 00:35 | entered it in pgAdmin.
| | 00:38 | Now we see in the init function we are
going ahead and creating the table, and
| | 00:43 | then in the main function I have this
little marker "database operations go here."
| | 00:49 | And I am just going to replace these
three lines, this foreach loop, with the
| | 00:54 | database operations for this example.
| | 00:57 | So we are going to start by
inserting a row into the table.
| | 01:01 | Now you will remember that this table
has a SERIAL PRIMARY KEY and id column
| | 01:07 | that's automatically incremented, and
as we insert this row, we want to get a
| | 01:13 | value back that's going to be that new ID,
because we don't know what the id is going to be.
| | 01:18 | So I am going to create a variable for
that, and we are going to insert a horse.
| | 01:21 | We need another animal,
so we will call this the horse_id.
| | 01:25 | And I am going to use the
db->sql_query value. And we will put our SQL inside
| | 01:35 | these quotes here, and we
will say INSERT INTO $tn--
| | 01:39 | that's our table name--and the
columns animal and sound, and VALUES
| | 01:50 | will get the SQL placeholders.
And then it's going to say RETURNING id, and
| | 01:59 | that is a syntax that is specific to Postgres.
| | 02:02 | That allows an insert statement, which
is normally not a query statement, not
| | 02:07 | a statement that returns any value, to
actually return the value of the next
| | 02:12 | sequential id. And this is specifically,
it matches up with the SERIAL PRIMARY KEY.
| | 02:18 | So you use the returning clause, and
you give it a column name. And I am just
| | 02:24 | going to go to the next line here for
our parameters, because remember, we need
| | 02:28 | two parameters for these
placeholders right there.
| | 02:32 | So those parameters are going to be
horse, and the horse has to make a sound, and
| | 02:38 | of course a horse is
going to sing the song for Mr.
| | 02:41 | Eddie, "A horse is a horse, of course, of
course," because that's the sound that horses make.
| | 02:50 | We are going to put in a MESSAGE, and we are
going to echo back the id number that we got.
| | 02:59 | We are going to say Added id number %d,
and that will be horse_id. And now we
| | 03:10 | are also going to add another record, but
this time we are going to use the CRUD syntax.
| | 03:15 | So you can see both ways of doing this.
| | 03:17 | One way uses the SQL, and the other
ways is arguably easier. Let's add a bird.
| | 03:21 | So we will give it a bird_id.
| | 03:24 | So we will use db->insert, and we will
give it an associative array. And the first
| | 03:34 | named element will be animal, and we
will call it bird. And then we will give it
| | 03:40 | a sound, and its sound will tweet,
because that's the sound that birds make.
| | 03:48 | Tweeting is not just for
Internet-crazed teenagers.
| | 03:52 | Again, we are going to say Added id
number, and this time it will be the
| | 03:59 | bird_id. And now we'll spit out how
many records there are in the table. And we
| | 04:17 | will go ahead and we will retrieve
the two rows based on their id number.
| | 04:21 | We are going to do this in two different ways:
| | 04:23 | we are going to do this once using
SQL and once using the CRUD methods.
| | 04:29 | So the SQL will use db->sql_query_ROW,
and it will say SELECT * From $tn WHERE ID =
| | 04:45 | and we will use the SQL placeholder
question mark, and we will pass in the
| | 04:49 | horse_id. And then we
will go ahead and display it.
| | 05:03 | Again, we are using those printf-like
placeholders, because message uses format
| | 05:09 | just like printf. And we will
dereference the row array. What we got back from
| | 05:14 | the SQL query row in $row
is an associative array,
| | 05:18 | so we de-reference it just like
any associative array in PHP.
| | 05:21 | Like that, $row'id;,
$row'animal', and $rowsound.
| | 05:38 | Now we are going to do exactly the
same thing using the CRUD methods, and you
| | 05:42 | will see how much easier it is.
| | 05:43 | We just say row
=$db->get_rec, and we pass in the bird_id.
| | 05:52 | And that's as simple as that.
| | 05:53 | So this line is basically the
equivalent of that. And then I'll go ahead and I
| | 06:01 | will just copy this message, because
that's going to be exactly the same, and we
| | 06:05 | should get the same results.
| | 06:07 | So I am going to go ahead and save this.
| | 06:08 | I am pressing Command+S here on my Mac.
And we will look at it again and just
| | 06:12 | make sure that there are any typos.
| | 06:14 | We have the horse_id =, and there is
our SQL INSERT INTO $tn, (animal, sound),
| | 06:20 | VALUES (?, ?) RETURNING id.
| | 06:22 | Of course, that returning clause,
that's Postgres-specific SQL right there.
| | 06:28 | We pass it the word horse and the sound
that the horse makes, which is the horse,
| | 06:33 | of course, singing the song. Added_id
number %d. There's the horse_id. Then we do
| | 06:39 | the same thing for the bird, except
this time we used the CRUD methods, insert
| | 06:43 | (array(animal, => 'bird', 'sound' => 'tweet'.
| | 06:46 | And we have the right number of
parentheses there. And we put out the message
| | 06:51 | for the bird, and then how many rows there
are in the table, and then we do our queries.
| | 06:57 | So we get SELECT * From $
tn WHERE id = ?, $horse_id--
| | 07:03 | that looks right--and the message for the
horse, and then we do to get_rec, which
| | 07:08 | is very simple, and the message for the bird.
| | 07:11 | So we will go ahead and we will save
that, pressing Command+S again, and we will
| | 07:15 | go and load it up in the browser.
| | 07:16 | There is insert, and there we have it.
| | 07:21 | So here we have, there are four rows in
the table to start, and that is actually
| | 07:25 | up here before our database operations go here.
| | 07:28 | So where the timer starts, it then
says there are %d rows in the table.
| | 07:32 | So that's always going to say 4,
because so that's how many are inserted in
| | 07:36 | the init down here: we have for animals.
| | 07:39 | So it starts with, There are 4 rows in the
table, and we Added id number 5, Added id number 6.
| | 07:44 | So that's that returning clause in the
insert statement--that's specific to Postgres.
| | 07:51 | That's how you get your serial ID.
| | 07:53 | Now there are six rows in
the table, and here they are.
| | 07:56 | We have the horse says "A horse is a
horse, of course, of course,...," and
| | 08:00 | the birds says "tweet."
| | 08:02 | The horse of course was done with
the SQL, and the bird was done with the
| | 08:07 | simple CRUD method.
| | 08:09 | We dropped the table and a
lapse time, 29 milliseconds.
| | 08:13 | So we can see the inserting data into a
table with Postgres in PHP is as simple
| | 08:17 | as using the SQL insert statement or
using the CRUD facility from the library.
| | Collapse this transcript |
| Getting data from a table | 00:00 | Retrieving data from your table with
Postgres in PHP is simply a matter of
| | 00:04 | using an SQL query.
| | 00:06 | Let's take a look at how this is done in PHP.
| | 00:09 | We're going to make a working copy of
the start.php here, and we're going to
| | 00:14 | name that retrieve, I before E.
We'll go ahead and open that in the text editor.
| | 00:23 | Before you continue, it's important that
you update the password in this file to
| | 00:26 | match the password you entered when
you created the SID user in pgAdmin.
| | 00:31 | You see here on line 14 I have foo.bar.
| | 00:34 | This is where you'll need to put the
password for your SID user exactly as you
| | 00:38 | entered it in pgAdmin. And come down
here to the "database operations go here."
| | 00:45 | The first thing we're going to do is
we're going to use the query row and get a
| | 00:52 | row, which will be a associative array;
| | 00:55 | $db->sql_query_row("SELECT * FROM $tn
WHERE animal = and the SQL placeholder,
| | 01:11 | which will get replaced with the string duck.
| | 01:13 | So this will retrieve the row for the duck.
| | 01:18 | If we look down here in our insert
statements, that's probably row number 3.
| | 01:22 | Then we'll go ahead and display it
with the message call. And so that will be
| | 01:32 | $row 'id', and I'm just going to cut
and paste here for the rest of them.
| | 01:36 | And because this is an associative array,
we just de-reference it like this, like
| | 01:40 | any associative array in PHP.
| | 01:43 | We have animal and we have
sound, and that is how we do that.
| | 01:51 | We can also get a single value, and
here we'll just use a message call.
| | 01:57 | We'll say value result:
| | 01:59 | %s for a string, and we'll just say $db
->sql_query_value("SELECT sound FROM $tn,
| | 02:16 | our table name, WHERE animal =?
| | 02:24 | And that question mark of course is
the SQL placeholder, and we're going to
| | 02:29 | give it the string dog.
| | 02:30 | So that will give us the sound that a dog makes.
| | 02:35 | Finally, oftentimes we're going to
want to simply get a whole bunch of rows,
| | 02:41 | and so here we can get all of them.
| | 02:43 | We can say foreach get_recs as row.
Actually, this is going to be exactly the
| | 03:02 | same as this one, just indent it.
| | 03:06 | Then for each row, it will go
ahead and display that line.
| | 03:10 | Now, you'll notice that we're calling
this get_recs in the place of an iterator,
| | 03:16 | in this iterative foreach, and that's
because the library has been written as
| | 03:20 | such that that conforms to the
iterator pattern. And it will simply get each
| | 03:25 | record one at a time, rather than
filling up a whole array and taking up a whole
| | 03:30 | lot of memory and a whole lot of
processor and then spitting out all the
| | 03:34 | records in a separate loop.
| | 03:36 | So this is actually a very,
very efficient way to do this.
| | 03:40 | So looking at it again, we're querying
a whole row here in the first one, and
| | 03:44 | we're putting that in an associative
array and displaying it one row at a time,
| | 03:50 | and then we're getting a
single value here, selecting sound.
| | 03:54 | And then finally, we're iterating through
the whole table using the CRUD method get_recs.
| | 04:01 | So go ahead and save this.
| | 04:02 | I'm pressing Command+S on the Mac here,
and we'll bring up the browser and go to
| | 04:08 | Chapter 1, and this is retrieve.php.
| | 04:12 | So here we have the third one is duck,
like we predicted that it would be, and
| | 04:17 | the value result is woof, like we
predicted that it would be, and then here is
| | 04:21 | all four of the records with the CRUD iterator.
| | 04:24 | So retrieving data with Postgres in
PHP is simply a matter of constructing
| | 04:30 | an SQL query just as you would in
another environment or using the CRUD
| | 04:35 | facility from the library.
| | Collapse this transcript |
| Updating data in a table| 00:00 | Updating data in your table is
simply a matter of writing the SQL and
| | 00:03 | passing the data in.
| | 00:06 | We'll start by making a working copy of
start.php, and we'll take a look at how
| | 00:10 | this is done in PHP.
| | 00:12 | We'll call this one update.php, and
we'll open that in the text editor.
| | 00:17 | Before you continue, it's important that
you update the password in this file to
| | 00:21 | match the password you entered when
you created the SID user in pgAdmin.
| | 00:26 | You see here on line 14 I have foo.bar.
| | 00:29 | This is where you'll need to put the
password for your SID user exactly as you
| | 00:33 | entered it in pgAdmin.
| | 00:35 | And scrolling down here to the
"database operations go here" section,
| | 00:42 | we'll start by grabbing a row using SQL.
| | 00:48 | So that will get us the row for the dog,
and we'll go ahead and display it with
| | 00:54 | the message function. And we'll break
out those variables from the row, starting
| | 01:10 | with the ID and the animal and the sound.
| | 01:19 | Now once we know the ID, we can put
that in a variable, and then we can update
| | 01:32 | it. And we'll do that with SQL here.
| | 01:43 | We'll use sql_do because in this
case we don't have any return value.
| | 01:51 | We'll use the SQL for updating, and
this is just standard SQL right here.
| | 01:56 | ("UPDATE $tn SET sound =, and we'll use
the placeholder there, and the dog will
| | 02:05 | now say bow wow instead of woof.
| | 02:09 | We also need a where clause, WHERE id =?,
| | 02:14 | and we'll need a second
parameter with the $id variable.
| | 02:19 | Now, let's do this again with the CRUD methods.
| | 02:22 | We'll start by assigning
the row. We'll use get_rec.
| | 02:30 | Since in this case we already
know the ID, we can just use it there.
| | 02:36 | We'll go ahead and display the values so far.
| | 02:41 | In this case, we'll display the bow
wow, and now we'll go ahead and update it
| | 02:46 | with the update method instead of the SQL.
| | 02:49 | So we'll call db->update, and we'll use
the ID to indicate which row we're updating.
| | 03:06 | And in the array, all we need to
specify is the column that we're updating.
| | 03:14 | We don't need to specify all of the columns.
| | 03:19 | This time it will just say ruff.
| | 03:22 | Now again, we'll just get the record
and display it using that same code from a
| | 03:27 | couple of lines above.
| | 03:28 | We queried the row, we got the record
for the animal dog, and we displayed it,
| | 03:35 | and then we updated it using
SQL--and this is very standard SQL;
| | 03:39 | there's nothing unique here for Postgres--
and then we went ahead and we queried
| | 03:44 | the record from the database, and we
displayed it, and then we updated it again,
| | 03:48 | this time with CRUD.
| | 03:50 | This time we're using this update function,
and then we use get_rec and display it again.
| | 03:55 | So let's go ahead and save this, pressing
Command+S and go ahead and run it in the browser.
| | 04:06 | And there it is!
| | 04:08 | The dog initially says woof, and then
we update it and then he says bow wow, and
| | 04:13 | then we updated with CRUD,
and then he says ruff.
| | 04:16 | Again, updating data in
our table is very simple:
| | 04:19 | simply use the SQL update statement
and pass the data, or we can use the CURD
| | 04:25 | facilities and an associative array.
| | Collapse this transcript |
| Deleting data from a table| 00:00 | To delete rows from your table, you
simply use the SQL delete statement, and
| | 00:05 | we'll take a look at that in PHP,
making a working copy here of start.php.
| | 00:10 | I'm going to call this one delete.php.
And of course when you're all done,
| | 00:15 | you're welcome to delete it.
| | 00:18 | Before you continue, it's important that
you update the password in this file to
| | 00:22 | match the password you entered when
you created the SID user in pgAdmin.
| | 00:27 | You see here on line 14 I have foo.bar.
| | 00:30 | This is where you'll need to put the
password for your SID user exactly as you
| | 00:34 | entered it in pgAdmin.
| | 00:36 | I'll start down here in the main
function, where we have the "database
| | 00:41 | operations go here."
| | 00:43 | We'll just delete this foreach, and
we'll go ahead and delete record 3.
| | 00:50 | So I'll say $id = 3 and message(
'deleting id %d' $id. And then we'll use the
| | 01:05 | sql_do("DELETE FROM $tn WHERE id =,
and we'll use the SQL placeholder
| | 01:16 | question mark there and type the variable
name after it. And then we can say 'after delete:
| | 01:26 | There are %d records in the table,'
and then we'll call count_recs.
| | 01:37 | Then we can also just go ahead and
display all the records here. And then we can
| | 01:48 | just do exactly the same thing again.
| | 01:50 | I'm just going to copy and paste all of this.
| | 01:52 | But instead of the sql_do, we're
going to call the CRUD method, which is
| | 01:58 | simply called delete.
| | 02:00 | Of course, record 3 will already have been
deleted, so we'll delete record 2 instead.
| | 02:08 | So now in our first little block here,
we're deleting using SQL, using a
| | 02:13 | standard SQL delete statement. And we
display a message that there are so many
| | 02:19 | records left, and we display all the
remaining rows in the table. And then we do
| | 02:23 | the same thing again using the CRUD
delete method. So let's see how this works.
| | 02:27 | We'll go ahead and save this pressing
Command+S, and we'll bring up the browser
| | 02:33 | and go to Chapter 01 and delete.php.
| | 02:38 | So it starts out with 4 rows in a table.
| | 02:39 | We delete ID number 3. After delete,
| | 02:42 | there are 3 records in
the table, and this is them.
| | 02:45 | We see that number 3 is
missing. And then we delete id:
| | 02:49 | 2 and after delete:
| | 02:50 | there are 2 records in the table,
and all that's left is 1 and 4.
| | 02:54 | So in order to delete data from your
table, you simply can use the SQL delete
| | 02:59 | statement, or you may use the delete
function from the CRUD facility in library.
| | Collapse this transcript |
| Creating a database library| 00:00 | As you've seen in this chapter, a
simple database wrapper library can be a
| | 00:04 | very convenient thing.
| | 00:05 | Let's take a closer look at the library
we've been using in this chapter so you
| | 00:09 | can see what's involved in creating your own.
| | 00:11 | So down here in this lib folder in
the Exercise Files, you'll notice a file
| | 00:17 | called bwPgSQL.php, and that's the library
that we've been using throughout this chapter.
| | 00:23 | I am going to open that in the editor.
| | 00:25 | I'm just going to say this at the outset: you
are welcome to use this however you see fit.
| | 00:31 | If you do redistribute it, if you do
make any modifications to it, I just ask
| | 00:35 | that you leave these comments up at the top
here, so people will know where it came from.
| | 00:41 | So it starts out by declaring the class
bwPgSQL and some private variables that
| | 00:46 | are used throughout the library,
and then we have the constructor.
| | 00:50 | You'll notice at the top of each function
I have a little usage so that when I
| | 00:54 | look at this I know how to
use that particular function.
| | 00:58 | So this is the constructor, and it
gets passed a user, a password, a
| | 01:02 | database, and a table_name.
| | 01:03 | You'll notice that there is a
default value for the table_name, so that
| | 01:06 | parameter is actually optional; the
other ones are not. Then I use PDO.
| | 01:11 | Throughout this library I'm using PDO.
| | 01:13 | PDO is PHP's portable
object-oriented database library.
| | 01:18 | I think it stands for Portable
Database Objects. And it makes it really
| | 01:23 | convenient to write these
wrappers and then simply modify them for
| | 01:27 | different databases.
| | 01:28 | I've got pretty much the same code
that I've written for MySQL and also for
| | 01:32 | SQLite, and all of these wrappers work
exactly the same, so I can simply unplug
| | 01:36 | one and plug in another one,
and everything continues to work.
| | 01:41 | I have a lovely little note here about
how PHP doesn't play nice with Unicode,
| | 01:45 | and the truth is is that it's
starting to get a little bit better.
| | 01:48 | They've been working really hard on it.
It seems like forever.
| | 01:51 | But the code hasn't really propagated, and
it's not really very consistent and done yet.
| | 01:55 | So it still makes sense to set
the client encoding to Latin-1.
| | 02:00 | Unfortunately, Latin-1 is ugly any
place except in the United States.
| | 02:06 | So here I've got some setters and
getters for the table_name, for the database
| | 02:11 | handle, for statement handles.
| | 02:14 | This allows a little bit of
extra flexibility in the library.
| | 02:17 | I rarely use any of those.
And then the actual fun begins.
| | 02:22 | The first method is sql_do, and you'll
notice that it takes a variable number of
| | 02:27 | arguments, and this allows us to use
those cool SQL placeholders, the question
| | 02:32 | marks for placeholders.
| | 02:34 | It returns a count of the rows affected.
And as you can see, this is really just
| | 02:39 | a matter of calling prepare
and execute from the PDO library.
| | 02:47 | Then I simply have wrapper for the
PDO's exec, which allows you to pass in a
| | 02:52 | string with multiple lines of SQL.
| | 02:55 | I recommend never using that,
but it's there for completion.
| | 03:00 | sql_query is pretty cool because it
returns an iterable statement handle.
| | 03:05 | This allows us to do cool things
like using it in a foreach loop.
| | 03:10 | query_all is another one of
those "don't ever use this one."
| | 03:14 | It basically calls fetchAll, and it
fills up memory with every row in a table
| | 03:19 | that matches the query.
| | 03:21 | So it's very, very easy for you
to crash your program with this.
| | 03:24 | I recommend that you never use it.
| | 03:25 | Again, it's just there to be complete.
| | 03:28 | sql_query_row returns one row from a fetch;
sql_query_value likewise returns one value.
| | 03:38 | You'll notice that all of these methods
will throw PDOExceptions in the case of errors.
| | 03:43 | And way up at the top, in the constructor,
I set the ERRMODE to ERRMODE_EXCEPTION,
| | 03:50 | which means that any of the PDO calls
themselves, like the Prepare, the Execute,
| | 03:56 | any of those will throw
PDOExceptions if there is a problem.
| | 04:01 | Then we have the simple begin_
transaction and commit, for transaction handling,
| | 04:07 | and now we have the CRUD stuff.
| | 04:08 | The CRUD stuff is where this gets really fun.
| | 04:11 | We actually construct SQL on
the fly from the parameter.
| | 04:16 | So the first one is get_rec.
| | 04:17 | This one is really simple.
| | 04:18 | Simply SELECT * FROM the table_name
WHERE id = the id value that gets passed in.
| | 04:25 | So that one is really simple.
| | 04:27 | Likewise, get_recs sets up a statement
handle and returns it so it's iterable,
| | 04:32 | and this allows us again to
use the cool foreach loops.
| | 04:37 | Again, the SQL construction
is pretty straightforward here.
| | 04:40 | INSERT is where things start
getting a little bit more interesting.
| | 04:44 | You'll notice that to construct the
query, I have to use joins from the
| | 04:49 | associative array that gets passed in.
| | 04:52 | I have that cool RETURNING. Just like
in our SQL in the insert movie, we're
| | 04:58 | using the Postgres extension
returning to return the value.
| | 05:02 | This makes this actually really much
more elegant than it does in another
| | 05:06 | library like MySQL where you have
to make a separate function call.
| | 05:11 | UPDATE likewise requires us to
create the SQL on the fly, based on
| | 05:18 | the associative array.
| | 05:20 | I find this just to be
really, really convenient.
| | 05:22 | It allows me to bury all of
this complexity in a library.
| | 05:28 | It allows me to not have to worry
about that kind of complexity in my code.
| | 05:32 | I can just pass around associative
arrays and everything just works.
| | 05:36 | So I only had to figure this
out once and then it's done.
| | 05:41 | Now, DELETE is very simple.
| | 05:43 | It's simply a delete statement in SQL.
| | 05:46 | There is a pseudo-iterator called get_next.
| | 05:48 | This comes in handy every once in a while,
when you don't want to use a foreach
| | 05:52 | loop, you want to be able to get
the next record adhoc and different types
| | 05:56 | of loops and in different types of places.
| | 05:58 | Likewise count_recs;
| | 05:59 | this is something that's a little bit
unusual for Postgres. SELECT COUNT(*) in
| | 06:04 | most databases is actually a very,
very efficient thing to do, and it gets
| | 06:09 | optimized out of the way very, very
efficiently. Because of the construction of
| | 06:14 | the Postgres databases--and they say
that it has to do with the fact that it's
| | 06:18 | object oriented--this is not
actually that efficient in Postgres.
| | 06:22 | Postgres does its writes
asynchronously and its reads asynchronously.
| | 06:26 | It doesn't actually keep track of how
many records are in a database at a given
| | 06:31 | time, or in a table at a given time, and
so this is not as efficient as it is in
| | 06:36 | some other databases.
| | 06:38 | And last_insert_id, again, just to be
complete, this is something that's very
| | 06:43 | convenient in other databases like
MySQL, and so I've implemented it here
| | 06:48 | just to be complete.
| | 06:51 | table_exists is a convenience function
for finding out if a table exists before
| | 06:55 | you go and create it, and in case for
some reason you don't want to do a drop
| | 06:59 | table if exists. And then there is a
little thing for getting the version and
| | 07:05 | the methods for the timer_
start and the timer_end.
| | 07:09 | So a database wrapper like this can
be a very convenient and useful tool.
| | 07:13 | Feel free to use this library for your
own projects, or to use it as a starting
| | 07:17 | point to create your own.
| | Collapse this transcript |
|
|
2. Creating a DatabaseUnderstanding databases and tables in PostgreSQL | 00:00 | As a starting point, it's good to
understand how a Postgres database works.
| | 00:05 | In Postgres a database is a general container.
| | 00:08 | It stores both data and metadata.
| | 00:11 | It includes the tables related to the
database, as well as metadata describing
| | 00:16 | permissions and other
attributes related to the database.
| | 00:20 | An application communicates with a
database driver that knows how to talk to
| | 00:24 | the Postgres server.
| | 00:26 | This allows the application to log
in to the server with the appropriate
| | 00:29 | credentials, and to send SQL statements
and receive replies, including rows of
| | 00:34 | data from the tables in the database.
| | 00:37 | The driver then communicates with
the server on behalf of the application,
| | 00:41 | exchanging the credentials, statements,
and data responses with the server.
| | 00:46 | The database in turn
contains tables, among other things.
| | 00:50 | Those tables contain the rows and
columns of data that make your application
| | 00:54 | do that thing it does.
| | 00:56 | So in order to create a database
application, you must create the database,
| | 01:00 | design and code the tables, using rows
and columns of relevant data designed to
| | 01:05 | interact and express the
intention of the application.
| | 01:09 | In this chapter, we'll look at how to
create databases and tables in Postgres.
| | Collapse this transcript |
| Creating a database| 00:00 | In order to create a database in
Postgres, you will need to have sufficient
| | 00:04 | permission, and while it's possible to
assign sufficient permission to a normal
| | 00:08 | user or, say, a user that's only logged
in from a particular application, it's
| | 00:14 | generally a bad idea.
| | 00:15 | So you'll normally need to have
superuser permission in order to create a
| | 00:20 | database. And given that restriction,
the easiest way to create a database is to
| | 00:24 | launch the pgAdmin application, where
you have superuser permission by default.
| | 00:30 | So here's the pgAdmin application.
| | 00:33 | I am going to double-click on our database here
| | 00:35 | so we get logged in, and click on Databases.
| | 00:39 | You can see here the databases that
we've already created when you installed
| | 00:42 | Postgres, and the Postgres database,
which is the default database it's the
| | 00:46 | administrative database--
| | 00:48 | you need that in order for the system to work,
| | 00:50 | so don't ever delete that one.
| | 00:52 | Now let's go ahead and create
a database. This is very easy.
| | 00:55 | We simply select Databases in the
object browser and press the magical "create
| | 00:59 | one of these" button, and that
pops up a new database dialog box.
| | 01:04 | We can type in the name of the database.
| | 01:05 | We will just call this foobar,
and we will type in an owner.
| | 01:09 | We will select web over here. And
really at this point, ninety-nine times out of a hundred,
| | 01:15 | you're just going to press OK, and it
will create that database. But let's just
| | 01:20 | take a quick tour through the
different things that you can do.
| | 01:23 | Under Definitions you can change the encoding.
| | 01:25 | There are a lot of law encodings available.
| | 01:27 | Of course UTF-8 is going to
be perfect for most purposes.
| | 01:31 | You can select a template. Because of
the object-oriented nature of Postgres,
| | 01:36 | databases inherit from other objects,
and these templates are what they inherit
| | 01:40 | from, for the most part.
| | 01:42 | Some of these templates you will
notice are other databases, and then there is
| | 01:46 | template0 and template1.
| | 01:47 | template1 is the default.
| | 01:49 | template1 actually inherits from template0.
| | 01:52 | In the default configuration of Postgres
as it comes out of the box, so to speak,
| | 01:56 | they're exactly the same.
| | 01:58 | So you can modify template1 if you
like, for whatever defaults you want to have
| | 02:03 | like perhaps you want a particular
language for stored procedures or something
| | 02:07 | like that, or perhaps you want some
defaults tables, you can modify template1
| | 02:11 | and then by default, whenever you create a
database, that database will follow that template.
| | 02:16 | You'll never mollify template0;
that's considered a bad thing to do.
| | 02:21 | Or you can simply use one of your
existing databases for a template.
| | 02:24 | In our case we are just
going to accept the default.
| | 02:26 | Tablespace is about where your data
is stored, and under most circumstances
| | 02:31 | you're not going to modify that;
| | 02:33 | all the data is going to be in place.
But with very, very large databases with
| | 02:37 | distributed storage you might want to change
that, and you might want to create a tablespace.
| | 02:41 | Coalition and Character type just have
to do with the character sets, and really
| | 02:47 | you're going to want to leave those
alone almost always and simply change the
| | 02:51 | encoding if you need to do that.
| | 02:54 | Under variables there are a lot
of variables that you can customize
| | 02:58 | your database with.
| | 03:00 | Most of these have to do
with things like optimizations.
| | 03:03 | You can change time zone on a
database basis, things like that.
| | 03:08 | For the most part, again, you're not going
to use this, but if you need to, it's there.
| | 03:12 | Postgres allows you to have a certain
amount of granularity for your privileges.
| | 03:16 | You can have some users that are
allowed all privileges, some users that are
| | 03:21 | allowed just to connect, some users
that are allowed to create data. And under
| | 03:26 | the SQL tab, this simply shows you the
SQL that you would use if you were going
| | 03:30 | to create this database,
| | 03:31 | like, for instance, in the command line
client or in a PHP script, if you have
| | 03:36 | those kinds permissions.
| | 03:38 | For our purposes, this is all just fine.
| | 03:40 | We are just going to select OK, and we
have created our database, and there it is.
| | 03:44 | It says foobar.
| | 03:46 | Once the database is created, I will
typically create an SQL file with the table
| | 03:50 | definitions and any initial data that
I want to populate those tables with.
| | 03:55 | In our Exercise Files of course, in the
SQL folder we have a few examples of this.
| | 04:01 | We did this when we installed
Postgres and one of these, albums-pg.sql,
| | 04:07 | let's take a look at that.
| | 04:08 | Bring that up in my editor,
and you can see what's in it.
| | 04:11 | I set the role to web so that the tables
as well as the database are owned by web.
| | 04:17 | Otherwise, if the Postgres superuser
is creating the tables, even though the
| | 04:21 | database is owned by the web role,
the tables might be owned by Postgres and
| | 04:26 | then a web user wouldn't be able
to modify them. So I set the role,
| | 04:31 | I drop if they exist, and otherwise
I create some views and these views,
| | 04:37 | we will be using these throughout the course.
| | 04:40 | These are views for viewing
tables and viewing databases.
| | 04:42 | These are views for viewing things from
the system tables in the Postgres database.
| | 04:49 | Then I create a couple of tables,
and then I insert some rows of data.
| | 04:54 | So it's a pretty simple script and yet
it's really very useful to do things this way.
| | 05:00 | So let's go ahead and see
how we would import this data.
| | 05:03 | I simply select the database and press
the SQL button and I get the SQL editor,
| | 05:08 | and then I can just open this file and
it reads the file in, and just press this
| | 05:15 | green Execute query button and
those tables are now created.
| | 05:20 | So I am going to go ahead and open a
new window here, and you will notice this
| | 05:23 | is a separate window.
That other one is still there.
| | 05:26 | I can close that. And I just
type in a couple of queries.
| | 05:30 | I can say SELECT * FROM view_tables,
and that's my view that will show me what
| | 05:35 | tables exist in this database.
And there they are. I have two tables:
| | 05:41 | one called album and one called track.
| | 05:44 | So if I want to, I can select from
album and I can see all the albums.
| | 05:50 | There is the title of the
album and the artist of the album.
| | 05:54 | This is not a terribly convenient
way to manipulate your database.
| | 05:58 | That's why I wrote the sid application.
| | 06:00 | Now you can see that we have a
number of albums in our table. And track,
| | 06:05 | these are the tracks on those albums,
and the table is called track, singular,
| | 06:10 | and press Go there.
| | 06:12 | And there is the tracks that are in that table.
| | 06:15 | So I am going to go ahead and close this,
and now we have populated our database.
| | 06:21 | We have given it a couple tables, we've
given it some views, we've given it some
| | 06:24 | rows of data, and now it's
ready to use with our application.
| | 06:28 | So for our purposes here, I am
going to go ahead and drop this table.
| | 06:31 | You'll want to know how to do
this anyway. As you're experimenting, as
| | 06:34 | you're originally designing your database,
you're going to create and delete some databases.
| | 06:39 | Deleting a database is called dropping it.
| | 06:42 | So to drop this database, I simply
select it here and press the trashcan icon
| | 06:47 | and it says, "Are you sure you wish to
drop database foobar?" and I say Yes.
| | 06:53 | And that database is now dropped.
| | 06:54 | So when I select Databases over here, we
see we just have our original four databases.
| | 06:59 | So, pgAdmin makes it very easy to
create databases and to manage their
| | 07:04 | permissions and their content, and the
included SQL query editor is a convenient
| | 07:09 | tool for defining and
populating the tables in your database.
| | Collapse this transcript |
| Defining a table in SQL| 00:00 | Creating a table in Postgres is
accomplished with the SQL CREATE TABLE
| | 00:04 | statement, and it looks like this.
| | 00:07 | We're here in the SID application, and
you'll notice I've got the test database
| | 00:11 | selected. And I'm going to go
ahead and create a table called foo,
| | 00:14 | so I will type CREATE TABLE. And I am
going to name that table foo. And I'm
| | 00:21 | going to give it a column definition here.
| | 00:23 | First column will be animal, and
that's a text column, and the second column
| | 00:28 | will be sound, and that is also a text column.
| | 00:31 | There are a lot of different column
types available in Postgres, and we will
| | 00:35 | cover what those are and how
they work in another chapter.
| | 00:39 | So I'll go ahead and press the Go
button now and lapse time 6 milliseconds, and
| | 00:45 | that table is now created.
| | 00:47 | If I try to create the table again,
if I press Go again, you'll notice I'll
| | 00:50 | get an error that that relation, as
it calls it, already exists and so it
| | 00:56 | cannot create that table.
| | 00:58 | And if I take a look at the tables that
are already defined in this database, by
| | 01:02 | typing SELECT * FROM view_tables;--and
this is a view that I've created that's
| | 01:09 | in all of the databases in our example
databases--and it will give me a list all
| | 01:13 | of the tables that are
available in this database.
| | 01:17 | So you'll notice that we have
three that are owned by web--
| | 01:20 | customer, item, and sale--and those are
the one that we populated the database
| | 01:24 | with initially when we installed
Postgres and created these databases,
| | 01:28 | and then the one that we just created
called foo, which is owned by sid--and sid
| | 01:33 | is the user that we're
logged in with in the SID program.
| | 01:37 | So now we have created that table foo.
| | 01:40 | We can put some data in it if we like.
| | 01:42 | And over here in my text editor
I've got open this 02-examples.sql from
| | 01:47 | the Chapter02 folder in the
exercise files, and down here I've got some
| | 01:53 | INSERT statements here.
| | 01:54 | I am just going to copy those and paste them in.
| | 01:58 | And I will press Go.
| | 02:01 | And you see that four queries were
performed, four rows affected, which means that
| | 02:04 | we actually inserted data
into four rows in this table.
| | 02:08 | And so now I can look at that data.
| | 02:09 | I can type SELECT * from foo;
| | 02:14 | and I see the rows of the table that we
just inserted are returned down there.
| | 02:19 | Now, as you noticed before--I am
going to go ahead and take my CREATE TABLE
| | 02:24 | statement again and paste that back in--
as we noticed before, if I try to create
| | 02:29 | this table again and press
Go, it gives me an error.
| | 02:33 | And so if you have a script, like a PHP
script for example that has some SQL in
| | 02:38 | it, and you want to make
sure that table is available,
| | 02:40 | you would like for that script to be
able to create the table. But if it just
| | 02:44 | says CREATE TABLE like this then
it will always give you an error.
| | 02:48 | Instead what you can do is you can
use this extension, which is a Postgres
| | 02:52 | extension; you can say
CREATE TABLE IF NOT EXISTS.
| | 02:57 | And this will just create it
if the table doesn't exist.
| | 03:00 | If the table already exists, it won't
create the table again, and it won't erase
| | 03:04 | the data in your database.
| | 03:05 | If I say Go, that table was left alone,
and if I put in my SELECT statement
| | 03:10 | again, our data is still OK.
| | 03:16 | On the other hand, if you want to make
sure that you are starting with a fresh
| | 03:19 | table every time, you want to know if
the table exists, and if the table does
| | 03:24 | exist, you want to drop that table
first and then go ahead and create it.
| | 03:28 | So you can do this with the DROP TABLE IF EXISTS foo.
So that's an extension to the drop table statement.
| | 03:38 | Normally you'd just say DROP TABLE
and it would go ahead and drop the table
| | 03:42 | unconditionally, but if the table
didn't exist, it would give you an error.
| | 03:46 | So this allows you to
conditionally drop the table.
| | 03:49 | And I am just going to paste in my
CREATE again, and I'm going to go ahead
| | 03:53 | and put in our SELECT after this, and you
can see that it will create an empty table.
| | 03:59 | So three queries performed. We dropped
the table, we created the table, and
| | 04:03 | selected Not return any data, so that
data is now lost because we dropped the
| | 04:08 | table that had the data in it.
| | 04:10 | So if I go ahead and just populate
that again--I'll copy these lines for the
| | 04:14 | SELECT--and so now what we have here
is, we drop the table if it exists and
| | 04:21 | we create a fresh table, and we insert
four rows into it and then display those rows.
| | 04:26 | So if I press Go, we have exactly four rows.
| | 04:30 | Now, because I am dropping the table
every time, it creates those rows fresh
| | 04:35 | every time in a fresh table.
| | 04:36 | So if I press Go again,
we have the same four rows.
| | 04:40 | If instead I don't do the DROP and I
do a CREATE TABLE IF NOT EXISTS then it
| | 04:48 | won't create the table if
the table is already there.
| | 04:51 | This is the way that you would do it
in an application that just wants to add
| | 04:55 | data to an existing table but create
that table if it doesn't already exist.
| | 04:59 | And so now when I press Go, we
get four additional rows and we get
| | 05:02 | four additional rows.
| | 05:04 | So creating a table in Postgres is
simply a matter of defining your columns
| | 05:08 | using SQL and managing those tables
using the conditional IF EXISTS, or IF NOT
| | 05:13 | EXISTS for your CREATE
TABLE and DROP TABLE statements.
| | 05:16 | More detail on the various data types
that Postgres supports will be found
| | 05:20 | later in this course.
| | 05:22 | Before we go here, we're going to go
ahead and just drop our table, so that we
| | 05:29 | leave this database in its original
state for the rest of the exercises in
| | 05:34 | this chapter.
| | Collapse this transcript |
| Creating a table in PHP| 00:00 | Creating a table in PHP is simply a
matter of using the Postgres interface to
| | 00:04 | create the table using SQL.
| | 00:07 | Let's take a look at an
example of how to do that.
| | 00:09 | Here is the Chapter 02
folder of the exercise files.
| | 00:12 | You'll notice that this is the copy
that's in the document root of the web server.
| | 00:15 | You'll notice I've turned on the path bar
at the bottom of this file window here
| | 00:21 | so that you can see that this is under
XAMPP and htdocs, and so this is where we
| | 00:27 | put a copy of the exercise files
that's accessible to the web server.
| | 00:30 | So I'm going to make a working copy of
start.php here, and I'm going to name
| | 00:35 | this createtable.php, and I'm
going to open that in my text editor.
| | 00:42 | Before you continue, it's important that
you update the password in this file to
| | 00:46 | match the password you entered when
you created the sid user in pgAdmin.
| | 00:51 | You see here on line 14 I have foo.bar.
| | 00:54 | This is where you'll need to put the
password for your sid user exactly as you
| | 00:58 | entered it in pgAdmin.
| | 01:00 | This is a version of the sandbox
frameworks that I've created for this course,
| | 01:06 | Most of the examples of this are in the
quick-start chapter, but all of the supporting
| | 01:10 | files for this sandbox are
in the free exercise files.
| | 01:14 | So, if you have this course, you
have those free exercise files.
| | 01:18 | They don't require Premium membership to have
access to them. And so this uses my bwPgSQL.
| | 01:25 | It's a wrapper for the PHP PDO interface
for Postgres, and the reason for that is
| | 01:31 | really just to make it simple, so
that we can focus on the SQL and on the
| | 01:37 | features of the database and how this
works, rather than all of the details of
| | 01:41 | PHP's PDO interface. And if you want
the details of the PDO interface, take a
| | 01:46 | look at that library, which again is
in the free exercise files with this
| | 01:50 | course, and you can see how the PDO
interface is used from within there.
| | 01:54 | So the first thing we're going to do
here is we're going to take the table name
| | 01:58 | and put it in a variable, and you'll
notice that that's defined actually up here
| | 02:05 | in these constant definitions TABLE_NAME test.
| | 02:08 | Then I'm going to issue a
drop table if exists statement,
| | 02:17 | so with the database handle sql_do,
and that allows me to just execute some
| | 02:24 | literal SQL, which is the way that we
do most things with a database in PHP.
| | 02:29 | I'll say DROP TABLE IF EXISTS $tn,
which is the table name, and then we'll go
| | 02:38 | ahead and create the table.
| | 02:39 | We'll use sql_do again, and we say
CREATE TABLE $tn. Then we put in our column
| | 02:49 | definitions id, and use SERIAL
PRIMARY KEY, which creates a automatically
| | 02:56 | incrementing integer--
| | 02:58 | we'll talk more about this in the
chapter on data types--and the animal and
| | 03:03 | sound, which are both text columns.
| | 03:10 | So now the database is created.
| | 03:12 | We're going to go ahead and
populate it and search some rows.
| | 03:16 | I'm a big fan of comments in my code.
And that's going to look like this.
| | 03:26 | INSERT INTO $tn (animal, sound) VALUES,
and we'll use the SQL placeholders there
| | 03:39 | so that we can just pass
those values as parameters.
| | 03:42 | So it will be a cat, and the sound
makes is purr, and then I can just copy
| | 03:48 | this and just change the
animal: cat, dog, duck, bear.
| | 04:00 | Dog says woof, and the duck
says quack, and the bear says grrr.
| | 04:06 | Now we'll just put out a message.
And again, this message, this is a function
| | 04:13 | here in my sandbox code.
| | 04:15 | You'll see down below here, page, set
variables, content, and there is message,
| | 04:23 | error message, and error down at the
bottom, and that just makes it convenient to
| | 04:28 | display messages on screen.
| | 04:30 | There are %d rows in the
table, $dbt->Count_recs.
| | 04:41 | And the message function, it works like printf;
| | 04:43 | it uses a variety of arguments,
so that you can do things like
| | 04:47 | replaceable placeholders.
| | 04:48 | Now we'll go ahead and display these
rows that we just added with a foreach loop.
| | 05:03 | So get_recs is a convenient function
here in my database wrapper library that
| | 05:08 | allows us to iterate through a table,
row by row, and it deposits those rows in a
| | 05:14 | PHP associative array.
| | 05:15 | So we'll go ahead and display that associative
array like this. And then we just name these.
| | 05:24 | This will be animal,
and this one here will be sound.
| | 05:29 | So that should be all there is to it.
| | 05:32 | Here we've created the table
using this SQL CREATE TABLE.
| | 05:36 | We drop table if it exists, so we make
sure that we have a fresh table each time
| | 05:40 | we run this, and then we insert some rows.
| | 05:43 | We count the rows in the
table and display them one by one.
| | 05:47 | So I'm going to go ahead and save this.
| | 05:48 | I'm pressing Command+S on my Mac.
| | 05:50 | I'm going to bring up the browser and
go to the exercise files and Chapter 02, and
| | 05:56 | here is my createtable.php that I
just created, and there it is, running.
| | 06:01 | So that's as simple as that.
| | 06:03 | I created the table.
| | 06:04 | I populated it with four rows.
| | 06:05 | I counted them, and there are the four rows
of data. Then I drop it in the elapsed time.
| | 06:12 | So creating a table in PHP is really as
easy as creating the table directly with SQL.
| | 06:17 | You may use this technique with whatever
database interface or library you're using.
| | 06:22 | I'm using this library that I created.
| | 06:24 | Obviously, you can create your own, or you can
use mine as a starting place to create yours.
| | 06:29 | This library is described in a lot more
detail later in the course. And then you
| | 06:33 | can use that library--either the one
you created yourself or use mine--for the
| | 06:37 | rest of your application.
| | 06:39 | It's really that simple.
| | Collapse this transcript |
| Creating indexes| 00:00 | Indexes are a useful tool for improving
performance when searching large stores of data.
| | 00:04 | They also have a performance cost on
inserting rows into those index tables, and
| | 00:11 | they have a storage-space
cost for the space of the index.
| | 00:14 | So there are some trade-offs there.
| | 00:16 | Indexes are created with
the CREATE INDEX statement.
| | 00:20 | So for example, going back to our example
SQL here from Chapter 2, we have this
| | 00:27 | table that we've been using, and let's
say it has got some rows of data in it.
| | 00:33 | If I wanted to create an index on
the animal column, I would do this:
| | 00:53 | CREATE INDEX, and then the name of the
index, and ON, and the table name with
| | 00:59 | the column in parenthesis. And I
say Go and it creates an index.
| | 01:04 | And now if I want to see my indexes, I
can use the view that I created and say
| | 01:09 | SELECT * FROM view_indexes.
| | 01:15 | I see a list of indexes, and one of
them is this idxAnimal that I just created
| | 01:20 | that's owned by sid.
| | 01:22 | If I want to drop that index, I can
drop it with the DROP INDEX statement, and
| | 01:33 | that drops the index. And now if I do
that SELECT again, view_indexes, you see that
| | 01:43 | that index is no longer there.
| | 01:45 | Now, there is a couple of other ways to
create indexes that are sort of specialized.
| | 01:49 | Take a look at one of these right now.
| | 01:53 | And this is the primary key.
| | 01:55 | Now, PRIMARY KEY is technically a
constraint, and it's typically implemented with
| | 02:01 | an index, as it is here in Postgres.
| | 02:04 | The PRIMARY KEY constraint says that
this column is going to be unique and it's
| | 02:10 | going to be non-null.
| | 02:12 | So if I create a table with a PRIMARY
KEY--and here I'll have to drop the table
| | 02:17 | first--and I create a table with that
PRIMARY KEY, now I will not be able to
| | 02:27 | insert null values--and I'll also
not be able to insert duplicate values.
| | 02:32 | So if I go ahead and insert these rows
into that table, I now have those four rows.
| | 02:37 | If I go try to insert them again,
I'm going to get errors: "duplicate key
| | 02:42 | violates unique constraint" for the animal
columns, and so those rows were not inserted.
| | 02:49 | There's only one set of these rows in here now.
| | 02:51 | SELECT * FROM foo and you'll see
we've only got one set of four rows.
| | 02:56 | I also will not be able
to insert null values here.
| | 03:01 | So if I try to insert a null value for
animal, that also violates the constraint.
| | 03:09 | And you'll notice, if we take a look at
the view_indexes view, that it has created
| | 03:20 | a primary key index for that table--it's
called foo_pkey--and you notice we have
| | 03:25 | it for some of these other tables as well.
| | 03:28 | Now, if I drop the whole table, DROP
TABLE foo, you'll notice that its indexes
| | 03:34 | get dropped as well.
| | 03:35 | So I'll go ahead and do that.
And now the table is gone.
| | 03:38 | SELECT * FROM view_tables. The table
itself is gone you seem and view_indexes, and
| | 03:48 | the index is gone as well.
| | 03:50 | Finally, there is one other type of constraint
here. Sometimes I don't want the primary key.
| | 03:55 | In other words, I don't want it to
restrict null values, but I do want it to
| | 03:59 | restrict values to being
unique--restrict duplicates.
| | 04:03 | So I can go ahead and I can say CREATE
TABLE foo with a unique constraint, and
| | 04:09 | that also creates an index.
| | 04:17 | And of course the reason that these
indexes get created for these constraints is
| | 04:22 | that a search has to be done for every insert.
| | 04:25 | And this is one of the things that also
makes it slower, is every time you insert
| | 04:30 | a row into a table with a unique
constraint the database actually has to search
| | 04:34 | that index and find out whether or not
that key already exists, and if it does,
| | 04:41 | then throw the exception and disallow
the insert. So that index actually gets
| | 04:47 | searched for every insert.
| | 04:49 | So let's go ahead and drop
this table one more time.
| | 04:53 | Make sure we get our database back to its
original state for the following movies.
| | 05:02 | Indexes can provide performance advantages.
| | 05:04 | They also have performance costs
associated with them. And they can be used for
| | 05:08 | constraints, as with unique.
| | 05:10 | In Postgres, indexes are created using
standard SQL, either CREATE INDEX or by
| | 05:16 | specifying one of the
constraints that creates an index.
| | Collapse this transcript |
| Indexing ID fields| 00:00 | Like most database systems, Postgres
provides a simple and straightforward
| | 00:04 | facility for creating automatically
incrementing integers for ID columns.
| | 00:09 | This facility is called Serial Primary Key.
| | 00:12 | I am just going to paste some SQL in
here, and you'll see that if we create a
| | 00:17 | table with a field that has a type of
Serial Primary Key that this can be
| | 00:23 | nicely used for an id field.
| | 00:26 | So let's go ahead and create
this and watch what happens.
| | 00:31 | Now the first thing we will notice
about this is when we take a look at the
| | 00:35 | index definition--go ahead and run that query--
| | 00:44 | you see that we get a primary key index.
| | 00:47 | And that's just we would expect if it were text
primary key or any other kind of a primary key.
| | 00:52 | So that's used for the constraint so
all those values are unique and non-null.
| | 00:57 | Now there is another behavior here
that's unique for serial columns.
| | 01:02 | Let's go ahead and insert some rows here,
and you will notice that these inserts
| | 01:08 | are only inserting two columns: animal and sound.
| | 01:11 | They're not inserting any
value at all in the ID column.
| | 01:15 | So I will go ahead and insert those--
| | 01:17 | you can see four rows affected--and we
will SELECT * FROM foo, and you see that
| | 01:25 | that first column, that id column, gets
a serially incrementing value for each
| | 01:31 | insert; each insert gives it a new value.
| | 01:34 | In fact, if we insert another row--and I'm
going to just add another keyword here.
| | 01:51 | This is another special
Postgres thing, RETURNING id.
| | 01:56 | Now usually an insert
statement is a non-select statement--
| | 02:00 | in other words, it's a statement
that does not return any value--but in
| | 02:05 | Postgres there is an extension, this
returning clause, which actually causes
| | 02:09 | insert to return a value.
| | 02:11 | So when I run this you will see we get
a value down here in the results. id 5.
| | 02:17 | So it's returned us a row with one
column called id and a value of 5.
| | 02:24 | So when I select all our
values again, you'll see that
| | 02:29 | that is actually the id column
for that new row that I added, 5.
| | 02:35 | So what serial does is it actually
creates a separate sequence generator and it
| | 02:40 | marks that sequence as owned by the column,
| | 02:42 | so if the sequence is dropped, if the
column is dropped, or if the table is dropped.
| | 02:47 | So you can actually have a serial
column that's not primary key, but it's most
| | 02:52 | common to use it also as a primary key.
| | 02:55 | So the common usage that you will see is
this one here, where that Serial Primary
| | 03:04 | Key is used for the id column.
| | 03:07 | Okay, let's go ahead and drop our
table, put our database back in its
| | 03:13 | original state. And the Serial Primary Key
feature in Postgres is simple to use and reliable.
| | 03:20 | This is especially useful when
creating relationships between several tables.
| | Collapse this transcript |
|
|
3. Data TypesUnderstanding data types| 00:00 | Postgres has a rich set of data types,
and while you may never use most of them,
| | 00:04 | it's a good idea to have an
overview of what's available.
| | 00:07 | Numeric types include three sizes of
integers--2-byte, 4-byte, and 8-byte sizes--
| | 00:14 | decimal or numeric for fixed precision
applications, like money, and note that
| | 00:19 | while Postgres has a money type, this is
depreciated so you should avoid using it.
| | 00:24 | You should use decimal or numeric instead.
| | 00:26 | Decimal and numeric are the same thing.
| | 00:29 | It has real and double types for a
floating point precision, and it has serial
| | 00:34 | types for automatically
incrementing unique identifiers.
| | 00:39 | Character types include the
standard VARCHAR, or Character Variant for
| | 00:44 | Variable Length Strings;
| | 00:46 | CHAR for fixed length strings; and TEXT,
which is actually equivalent to VARCHAR
| | 00:52 | with no maximum length specified.
| | 00:55 | It's useful to note that the fixed-
length strings are actually less efficient in
| | 01:02 | Postgres than the VARCHARs.
| | 01:05 | The VARCHARs and the text are more
efficient than the fixed-length character
| | 01:10 | strings, although the fixed-length
character strings can be more efficient with
| | 01:14 | storage depending on the application.
| | 01:16 | So it's important to look at your
application and understand what you are looking
| | 01:19 | for: efficiency and speed, or storage
space. And I think most of the time you
| | 01:24 | will pick the text type or the VARCHAR type.
| | 01:27 | It's also worth noting that with
VARCHAR you can specify a maximum length, and
| | 01:32 | this may be important in some
applications, because that constraint gets
| | 01:36 | imposed by the database,
| | 01:38 | although in most cases where you are
running your database from an application
| | 01:42 | it may actually be more efficient for
you to implement that constraint in your
| | 01:46 | host language than in the database.
| | 01:48 | So again this is something to look at
in terms of performance and convenience,
| | 01:53 | but I think most of the time of you
look at all these trade-offs you are going
| | 01:56 | to select the text type for almost all text.
| | 01:59 | Postgres also supports the
standard date and time types:
| | 02:02 | timestamp that stores both date and time,
| | 02:05 | date for dates alone and time for time alone,
and an interval type for a time intervals.
| | 02:12 | Just a few other data types that are supported.
| | 02:15 | There are binary types, and these are packed
binary so you get one bit per binary value.
| | 02:21 | It's extremely efficient in
storage space. Boolean types,
| | 02:25 | now this is different than binary types.
| | 02:27 | Boolean types are for logical Booleans,
and this is basically true or false, and
| | 02:33 | these take up one byte of
storage per Boolean value.
| | 02:36 | So they're not efficient on storage,
but they are convenient for use.
| | 02:39 | And there is enumerated types for enumerations.
| | 02:43 | So Postgres has a rich set of data
types available, and while you may never use
| | 02:48 | most of them, it's a good idea to
have an overview of what's available.
| | Collapse this transcript |
| Storing numbers with INTEGER| 00:00 | Integer types are useful wherever you
need to store integer-numeric values.
| | 00:04 | In fact, they are commonly used for
fixed-precision storage, even where those
| | 00:08 | values have a decimal part.
| | 00:10 | Here we have a table definition
with two columns with integer types.
| | 00:16 | I want to go ahead and select
Go, and that creates that table.
| | 00:20 | Then come over here to my
03-examples.sql from the Chap03 in the exercise files.
| | 00:27 | Down here at the bottom I have some
inserts with various integer values.
| | 00:35 | Now the basic integer type in Postgres
is four bytes long, which means that this
| | 00:41 | third set of values is the minimum and
maximum size number that can be stored
| | 00:46 | in a four-byte integer.
| | 00:48 | These are signed integers.
| | 00:50 | So minus this big number to plus that
big number is as much as we can store.
| | 00:55 | So this fourth one should generate an
exception, because that's a number that's
| | 01:00 | too big to store in a four-byte integer.
| | 01:03 | So I created this table with a
standard integer type, which is four bytes,
| | 01:07 | so the first three inserts should be fine
and the fourth one should create an error.
| | 01:11 | So we will go ahead and select Go and
sure enough, query number four: integer out of range.
| | 01:16 | Now when I do a select, see,
we have those three rows.
| | 01:24 | So let's go ahead and drop this table
and create another one. Go back in here,
| | 01:34 | and we will go up to the top,
and we are going to use SMALLINT.
| | 01:37 | SMALLINT is a two-byte integer,
| | 01:42 | so that should give us even fewer of
those values. And let's go down to the
| | 01:46 | bottom here and get all four these
inserts, and we will just drop all these
| | 01:49 | in here, and we will do all this at once.
And we will do a select, and we will do a drop table.
| | 01:56 | So now we have got all of it in there
and we select Go and it creates the table,
| | 02:01 | and it gives us these two
errors: query number five and query number six.
| | 02:04 | So that's these two rows and sure enough,
it only stored the first two rows of data.
| | 02:10 | So a two-byte integer allows this
range here, from -32,000 to plus 32,000.
| | 02:17 | So if we want to be able to handle
all of these values, even up to this nine
| | 02:21 | bazillion, or whatever it is, large,
large number that is an eight-byte integer.
| | 02:28 | Instead of SMALLINT we use BIGINT, and
put that one over here too, and run this
| | 02:35 | again, and now we have no errors,
and we've got all of those values.
| | 02:39 | So for most applications you'll probably
just use the four-byte integer, and that's in
| | 02:43 | most circumstances going to be the most
efficient, because it's the natural word
| | 02:47 | size on most modern computers today.
| | 02:50 | But it's still good to know
that we have these other ones.
| | 02:52 | You can use SMALLINTs in places where
storage is as at a premium and you want to
| | 02:57 | make sure that you are using your
space and your disk storage in the most
| | 03:00 | efficient way, and the BIGINTs for
those cases where you just have to store
| | 03:04 | very, very large integer numbers.
| | Collapse this transcript |
| Storing decimal numbers with NUMERIC| 00:00 | Postgres provides a numeric type
for storing fixed-precision numbers.
| | 00:05 | Here we have a table being created with
two columns, and each of them is of the
| | 00:09 | numeric type, and we are inserting the
same values that we have been using for
| | 00:13 | all of the fixed-precision and
integer numbers throughout this chapter.
| | 00:17 | And we go ahead and select them from the
table so we can see what they look like
| | 00:21 | and then drop the table. So we'll go ahead
and press Go and you see that all of these
| | 00:25 | values were successfully inserted
into the table and are all being read and
| | 00:29 | displaying properly.
| | 00:30 | Now the point of numeric is
actually to provide fixed precision.
| | 00:35 | So if I were to say, for example,
12, 2--and we will do the same thing over
| | 00:41 | for this other one--
| | 00:42 | what this does is is it provides
space for twelve digits of precision, with the
| | 00:49 | decimal place two positions from the right.
| | 00:52 | So in other words, it will be ten
digits of decimal place and two digits.
| | 00:57 | So this bottom number
obviously is going to be too long.
| | 00:59 | Go ahead and select Go and sure enough,
we see that "A field with precision 12,
| | 01:05 | scale 2 must round to an absolute
value less than 10 to the 10th," in other
| | 01:11 | words, 10 digits of precision
to the left of the decimal point.
| | 01:15 | And so that last row did not get inserted.
| | 01:18 | If instead, we just change this 12 to
64 and do the same thing over here and
| | 01:26 | press Go, then we see that
we get all of the values.
| | 01:29 | So there is an important distinction to be
made here, precision versus resolution.
| | 01:35 | Precision means how many significant
digits are guaranteed to be accurate?
| | 01:39 | Resolution means how big
of a number can you store?
| | 01:43 | Numeric and integer fields are
high precision and low resolution.
| | 01:49 | Real, floating point, double precision,
those are the high-resolution data
| | 01:53 | types, but they have low precision
and so we will be looking at those in
| | 01:57 | the next movie.
| | Collapse this transcript |
| Storing floating-point numbers with REAL| 00:00 | Postgres provides two sizes of floating-
point storage: REAL and DOUBLE PRECISION.
| | 00:05 | So here we have a table with a REAL
type and a DOUBLE PRECISION type, and we're
| | 00:10 | inserting these same values we've
been using throughout this chapter.
| | 00:13 | So let's go ahead and
take a look at the results.
| | 00:16 | There we see we're successfully
storing all four rows of numbers, and if you
| | 00:21 | look at these last couple of rows,
you'll see that the way they're being
| | 00:24 | displayed is with not necessarily all
of the significant digits, but with the
| | 00:30 | scientific notation e, +, and the
number of positions to the right where the
| | 00:34 | decimal point would move.
| | 00:36 | So what this means is that
floating-point numbers are stored with a larger
| | 00:42 | scale, that is, a larger
magnitude, but a lower amount of precision.
| | 00:47 | In other words, there are only so
many significant digits where they're
| | 00:50 | guaranteed to be accurate.
| | 00:52 | So let's take look at another
example that will illuminate this effect.
| | 00:57 | I'm going to create this table all over again.
| | 01:04 | And in this case, we're going to use
two numeric columns and two real, or
| | 01:10 | floating-point, columns.
| | 01:11 | We're using NUMERIC so that we can
have fixed precision, and we're using
| | 01:16 | floating point so that we can have
greater magnitude but with less precision.
| | 01:20 | And then we're putting four values
into it, exactly the same for the numeric
| | 01:24 | and the floating-point.
| | 01:25 | So we'll go ahead and select Go and
we get the results that we expect.
| | 01:29 | These decimal numbers, these numeric
numbers, have ten significant digits of
| | 01:34 | precision and two places for the decimal point.
| | 01:38 | And so they're displayed here 0.10,
0.20, exactly as we would expect, and the
| | 01:44 | floating-point numbers are
simply displayed as 0.1 and 0.2.
| | 01:49 | Now the truth is that these
values are not all that they seem.
| | 01:59 | If instead of just selecting all of
the columns, if instead we add these two
| | 02:04 | numbers up and see if they equal to 3,
in other words, da is 0.10, db is 0.20,
| | 02:11 | da+db should be 0.30.
| | 02:13 | So I'm testing it here.
| | 02:15 | This is a Boolean test, and we should get a 1
if it's true or a blank cell if it's not true.
| | 02:22 | So if I say Go here,
we get 1. da+db is equal to 0.30.
| | 02:30 | Now here's the fun part.
| | 02:32 | We do the same thing using
the floating-point columns.
| | 02:36 | fa, fb, these are the ones
that are defined as real.
| | 02:40 | We would also still expect this test, fa+fb
equals 0.3 to be true, right? Well, it's not.
| | 02:48 | You notice we get a blank column there on
the right, and that means that it's not true.
| | 02:53 | So what is it, if it's not 0.3?
| | 03:00 | What this here does is this takes that
addition, fa+fb, and it formats it with a
| | 03:05 | whole lot of significant digits and
in engineering notation, or scientific
| | 03:12 | notation, with the E at the end.
| | 03:13 | And so that's what this to_char does
is it formats it so that you can see it
| | 03:18 | with a lot of precision and
see what that number actually is.
| | 03:21 | And when I select Go, you see that
it's 3 to the -01, but only to one, two,
| | 03:27 | three, four, five, six significant
digits, and then it starts a straying, and it
| | 03:32 | becomes a little bit less accurate.
| | 03:33 | If we make these DOUBLE PRECISION, it
will be better, but it's still not going to be 100% accurate.
| | 03:44 | You see now we get a lot more 0s, but
we've still got some problems down here.
| | 03:48 | We're using larger floating-point
numbers so we get more resolution, but we're
| | 03:53 | not really getting accuracy. Because
when it comes down to things like money, if
| | 03:57 | you are adding and subtracting large
columns of money day in and day out, say
| | 04:02 | for a bank, and you're introducing
these miniscule errors, if you've got
| | 04:08 | billions of transactions going on,
you're going to be off by millions of
| | 04:12 | dollars this way or that way.
| | 04:13 | This is the point, is you cannot
use floating-point storage for money.
| | 04:19 | You must use NUMERIC for money, because when
we do this with the NUMERICs, we get true.
| | 04:31 | That da+db is 0.30. Guaranteed. Always.
| | 04:38 | Now it's worth pointing out that
earlier versions of Postgres have a
| | 04:42 | specific money type,
| | 04:43 | and that has been depreciated, and instead
you're supposed to use the NUMERIC type.
| | 04:47 | And the NUMERIC type is exactly
designed for applications where you need
| | 04:52 | precision as opposed to magnitude or resolution.
| | 04:55 | You use the REAL or DOUBLE PRECISION
type where you need magnitude or resolution
| | 05:01 | and you're willing to sacrifice a
little bit of precision for that.
| | 05:04 | And that's great for engineering
applications where you can take that
| | 05:07 | variance into consideration.
| | 05:10 | But for applications where you're
going to be adding and subtracting many,
| | 05:14 | many, many, many rows of numbers over long
periods of time and expecting to get accurate results,
| | 05:20 | you need to use precision instead of resolution,
| | 05:23 | and so you should use the NUMERIC
types for those kinds of applications,
| | 05:26 | especially in accounting and banking.
| | Collapse this transcript |
| Storing text| 00:00 | Postgres supports both fixed
and variable storage for strings.
| | 00:04 | Here we are creating a table, and that
table has three columns, and they are all
| | 00:07 | specified as the text type. And we're inserting
into each column of a row a thirty-six-character-long string.
| | 00:16 | And so when I press Go here, you see
that that succeeds, and it works exactly as
| | 00:21 | we expect, and we get the whole
thirty-six-character string in each of these columns.
| | 00:27 | Now let's go ahead and edit this table
definition. We'll make this one here VARCHAR(16).
| | 00:34 | That's not going to give us
enough room for that string.
| | 00:37 | We'll see what happens. And we'll make
this one here a CHAR(16). And now when I go
| | 00:44 | ahead and execute, you see that we get an error.
| | 00:47 | So we are getting an error on the
VARCHAR(16) because that string is
| | 00:53 | thirty-six characters long.
| | 00:54 | So I am going to go ahead and take off
the 16, which makes VARCHAR exactly like
| | 00:58 | text and we'll go ahead and press Go
again. And now we see we are getting an
| | 01:03 | exception for it being too long for
the character, which is fixed at 16.
| | 01:08 | So this just isn't going to work.
| | 01:10 | We'll put his 16 back in, and we are
just going to change out this INSERT. And I
| | 01:15 | am going to over to my little cut and
paste area here that you'll find in your
| | 01:19 | Exercise Files in Chapter 3, under the
file name 03-examples.sql. And I am going
| | 01:26 | to get these twelve-character strings instead,
which just happens to be my name, and
| | 01:30 | I'll go ahead and paste that in there,
and select Go, and now everything is
| | 01:34 | working exactly like we expect.
| | 01:37 | So what we see here is that we can
constrain the size of our strings to either a
| | 01:43 | variable storage space, where in this
case this twelve-character string is being
| | 01:49 | stored and it's taking up twelve
characters plus a length byte--
| | 01:52 | it's actually probably an integer.
And so it's being a little bit efficient
| | 01:57 | in storage space. And this one here
is twelve characters and that's actually
| | 02:01 | getting padded with nulls, and so it's always
going to take up sixteen characters' worth of storage.
| | 02:07 | The thing to know about this is how
Postgres works with these different types.
| | 02:12 | The text type, which is the equivalent
of VARCHAR without a link specification,
| | 02:18 | is the most efficient type in terms of
processing speed, because the storage
| | 02:23 | engine is actually optimized for that.
| | 02:26 | So the text type is the most efficient.
| | 02:28 | The VARCHAR is effective for providing
a constraintm, and it's just a little bit
| | 02:33 | less efficient in terms of processing speed.
| | 02:36 | And the fix size C-H-A-R, CHAR, with a
constraint is the least efficient, and
| | 02:44 | actually what it does is it makes sure that
the storage is always exactly the same size.
| | 02:49 | So you're always going to be storing sixteen
characters' worth of string, even if your
| | 02:53 | actual data a smaller size than that.
| | 02:56 | And it still gives you constraints.;
| | 02:57 | it still gives you the exception processing
if you try to store more than that in there.
| | 03:02 | So that's what you need to
understand about the character types.
| | 03:05 | You have text for free-form storage,
VARCHAR for storage with constraints, and
| | 03:11 | CHAR for specific fixed-length
storage of text and string data.
| | Collapse this transcript |
| Storing Boolean values | 00:00 | Postgres provides a dedicated Boolean type.
| | 00:03 | It's is not really very efficient for
storage--it uses an entire byte for each
| | 00:07 | value--but it's good to know how it
works so that you can use it in other non-storage contexts.
| | 00:12 | So here's a table with two columns,
both of the Boolean type, and you'll notice
| | 00:16 | the one of them spells out the word
BOOLEAN and one of them is using the
| | 00:19 | abbreviated BOOL. And both of these are
acceptable and they both work. And then I
| | 00:26 | have inserted some various values.
| | 00:27 | You will notice that you can
use true and false as keywords.
| | 00:31 | You can use 1 and 0 as string values.
| | 00:35 | You can use true or the letter t,
also strings; false, the letter f; yes, the
| | 00:41 | letter y; no, the letter n; and these
will correspond to Boolean values.
| | 00:46 | So go ahead and select Go,
and you see here are our values.
| | 00:50 | So we get a one for true and we get a
blank for false. And so in the first
| | 00:56 | column we have the keywords TRUE
and FALSE, so we get a 1 and a blank.
| | 01:01 | In the second row we have the '1' and
'0' in quote marks for strings, and we
| | 01:06 | get a 1 and a blank.
| | 01:08 | In the third row we have 'true' and
't' so they are both 1, and then we have
| | 01:13 | 'false' and 'f' and they both blank.
| | 01:14 | We have 'yes' and 'y' and they are both true, and
we have 'no' and 'n' and they are both blank.
| | 01:21 | And so these are the Boolean values,
and this is how they get returned.
| | 01:25 | Now like I said, you are not really
necessarily going to use these for storage.
| | 01:29 | But you can and it works and if
storage space is not an issue for you, it's
| | 01:34 | certainly effective.
| | 01:35 | But really the value of
this is just understanding,
| | 01:39 | when you do something like this, you
have a value returned from a SELECT
| | 01:48 | statement or from some query that is
being evaluated as a Boolean value.
| | 01:56 | The result there is blank,
and that means that it's false.
| | 01:58 | On the other hand, if its b
equals b, then you will get a 1.
| | 02:03 | And so it's good to understand how the
BOOLEAN type works, even if you're not
| | 02:07 | using it in a storage context.
| | Collapse this transcript |
| Storing dates and times| 00:00 | Postgres provides a simple set of time
and date types for storing and working
| | 00:05 | with time, dates, and intervals.
| | 00:08 | The most common of these is probably
the timestamp type, which stores both a
| | 00:13 | date and time, with or without time zone.
| | 00:15 | So here we've created a table with two columns:
| | 00:18 | a is the regular timestamp and b is a
timestamp with the time zone. And we're
| | 00:24 | inserting into it some values. And it's
useful to note that these were all 3 PM
| | 00:30 | in two different time zones.
| | 00:32 | The first row is 3 PM as 15:25 in
Eastern Daylight Time, or 3:25 PM spelled out
| | 00:41 | that way in Eastern Daylight Time; and
the second row is 3:25 PM as 15:25 UTC,
| | 00:49 | UTC being the international designation for
Greenwich Mean Time, or spelled out as 3:25 PM.
| | 00:57 | And as Postgres works, there are a lot
of different ways that you can specify a
| | 01:01 | time string and it will interpret
most of them really, really well.
| | 01:06 | So here I've just used two of them
with the 24-hour time and the 12-hour time
| | 01:11 | with a AM or PM designation.
| | 01:14 | So we'll go ahead and run this, and
it's interesting what happens.
| | 01:17 | The a column has no time zone
associated with it, and the b column is storing
| | 01:22 | with time zone. And it's using the
default time zone that was installed when we
| | 01:28 | installed Postgres on this machine,
which is set for Los Angeles, California. And
| | 01:33 | as I'm recording this, that is Pacific
Daylight Time, which is seven hours early
| | 01:39 | from UTC, and so it gets
this -7 here with the time zone.
| | 01:44 | So you'll notice in the A column it
says 15:25 for both of them. Even though we
| | 01:49 | specified one as Eastern Daylight Time,
we specified one as UTC, it ignored the
| | 01:54 | time zone and it simply
stored the time and date--
| | 01:58 | October 7th, 2011 at 3:25 in the afternoon.
| | 02:03 | In the B column it stored it with the
time zone, but it's displaying it in my
| | 02:07 | local time zone, so it's
actually done the conversion for me.
| | 02:11 | Eastern Time zone is three hours ahead
of me, so 15:25. There would be 12:25 here
| | 02:17 | on the West Coast. And it's seven hours
from UTC so 15:25-7 would be 8:25, and
| | 02:24 | that's exactly what's being displayed here.
| | 02:27 | So that's how a timestamp works, and
timestamp is really, really useful. I use it a lot.
| | 02:31 | I'll use it for logging events.
| | 02:34 | I'll use it just by routine.
| | 02:35 | I will stick it in lot of tables
just so I have a record of when rows
| | 02:39 | were created or updated.
| | 02:41 | It's really, really convenient to use.
| | 02:44 | There are some other data types
associated with date and time.
| | 02:47 | You can do the time by itself, so I'm
going to go ahead here and just paste that
| | 02:52 | in. And we'll use exactly the same
inserts, but just with time columns, and it
| | 02:57 | will discard the date part
and just use the time part.
| | 03:02 | So without the time zone it's just
storing 15:25 for both of these rows, and with
| | 03:07 | the time zone it's actually storing it
with the time zone and displaying with
| | 03:12 | the time zone and not
doing the conversion for us.
| | 03:14 | So -4 is EDT and +0 is UTC. And likewise
there is a date type, and you'll notice
| | 03:24 | that the date type does not
have time zone available for it.
| | 03:29 | There is no "with time zone" clause
available for the date type, and that makes
| | 03:33 | sense, because of course a date does
not have a time zone--it's not a time.
| | 03:38 | So when I execute this query with the
date type, it just stores the dates, and it
| | 03:42 | ignores the time altogether.
| | 03:45 | And our final type here is the
interval type. And of course, these aren't
| | 03:52 | intervals, so if I run this, I'll just
get errors, because I can't store absolute
| | 03:56 | times into intervals.
| | 03:58 | We have some examples of intervals
here, and I'll just pop those in.
| | 04:04 | We have 1 day, 1 week, 1 month and 1
year, and when I go ahead and run that, I
| | 04:11 | get 1 day, seven days for one
week, 1 month, and 1 year for those.
| | 04:16 | And also, if you like, you
can do something like this.
| | 04:22 | You can use the AGE function to find
the difference between two dates, and that
| | 04:27 | should be 1 day. Or you can simply
store a string like this, 2 days.
| | 04:33 | So we have 1 day because we have the
difference between two dates, and we have 2 days.
| | 04:38 | So that's a quick overview of the date
and time types available for Postgres.
| | 04:42 | Of course, you can store these values,
or you can use these values simply in
| | 04:47 | queries.
| | Collapse this transcript |
|
|
4. Storing and Reading DataStoring and deleting data with INSERT, UPDATE, and DELETE| 00:00 | Storing and deleting data in Postgres
is pretty much like it is in other SQL
| | 00:04 | relational databases.
| | 00:06 | Let's take a look at the basics.
| | 00:07 | Here we're creating a table with three columns,
| | 00:11 | a, b, and c, and we're inserting
values a, b, and c into five rows.
| | 00:15 | We select those rows and then drop
the table, and there is our results,
| | 00:20 | we have the headings a, b, and c and
the five rows of a, b, and c. So that very
| | 00:26 | simply is how you store data in a table.
| | 00:29 | You create the table with the
appropriate column types and you insert those
| | 00:33 | types of data into the respective
columns in each row, row by row, using
| | 00:39 | the INSERT statement.
| | 00:40 | Of course that's not the only
way to get data into a table,
| | 00:44 | you can also do something like this.
| | 00:46 | Let me go ahead and write in here.
| | 00:50 | Before we drop that table, I'm going
to create a table b with columns d, e,
| | 00:54 | and f and we're going to INSERT INTO
b FROM a, and so that's how we do that
| | 01:01 | using what's called a subselect to select
everything from a and insert it right into b.
| | 01:06 | Then we'll go ahead and SELECT *
FROM b, so you can see that, and then
| | 01:12 | we'll drop them both.
| | 01:18 | So there is table b and it has got
columns d, e, and f, and you'll notice it has
| | 01:22 | exactly the same data from a.
| | 01:25 | But wait, there's more.
| | 01:27 | We can also do something like this.
| | 01:28 | INSERT INTO b (f, e, d) SELECT * FROM
a. So we're turning the order around.
| | 01:38 | Instead of d, e, f, it's f, e, d, and
yet we're selecting * FROM a, so that's
| | 01:43 | going to be a, b, c, and so we'll have
five more rows with everything reversed.
| | 01:49 | I'll go ahead and run that.
| | 01:51 | And when we scroll down here, you'll
see the first five rows are a, b, c and
| | 01:56 | the second five rows are c, b, a.
In fact, we can do this even more
| | 02:01 | convolutedly if we want to.
| | 02:05 | If we reverse the order of both f, e,
d and c, a, b, you'll find that it will
| | 02:11 | come out in the right order.
| | 02:13 | And so we'll go again, and now the first
five rows in our second table are a, b,
| | 02:17 | c, the second five rows are c, b, a,
and the third five rows are b, a, c,
| | 02:23 | because we did it in this order c, a, b from a.
| | 02:28 | So as you can see, there is number of
different ways you can insert data into a
| | 02:32 | table, including copying that data
from another table with subselects.
| | 02:37 | Now let's take a look at how you
update data that's already in a table.
| | 02:41 | We'll go ahead and we'll copy this
UPDATE example from our examples file in
| | 02:46 | Chapter 04, Command+C to copy it, and
then going back to the browser and select
| | 02:51 | all of this and paste it in, and
we'll take a look at how this works.
| | 02:55 | First, we're creating a table t with
the SERIAL PRIMARY KEY for the id and a
| | 03:02 | quote and byline for TEXT.
| | 03:03 | And so we're just going to be
inserting the quote and the byline, and we've
| | 03:07 | got Aye Carumba!
| | 03:08 | from Bart Simpson and a few other
interesting quotes, and then we're going
| | 03:12 | to select from that.
| | 03:14 | And why don't I go ahead and take out
these other lines for now, and we'll just
| | 03:18 | run this much of it so we
can see that that works.
| | 03:20 | And there we have four different rows
in this table with quotes and bylines.
| | 03:26 | Now, if we want to change
one, we can do it like this.
| | 03:31 | We update the table, UPDATE t SET
quote = and change it WHERE id=4.
| | 03:38 | So for the fourth row instead of saying
"I'll be back" The Terminator says "Hasta
| | 03:43 | la vista, baby." and we select Go.
| | 03:47 | And you see, we have the first query.
| | 03:50 | It's I'll be back, and the
second query it's Hasta la vista, baby.
| | 03:53 | So we updated that row and you
notice we have this WHERE clause.
| | 03:57 | If we did not have that WHERE clause,
well, why don't I go ahead and take it
| | 04:01 | out, and you can see what happens.
| | 04:03 | It will change all of the
quotes in the entire table.
| | 04:07 | And if I take out the WHERE clause in
the SELECT here, then you'll be able to
| | 04:11 | see the whole table,
| | 04:13 | And you can see that they
all say Hasta la vista, baby.
| | 04:16 | But if I put in that WHERE clause here,
id=4, then you'll see that just that
| | 04:22 | last one, just number 4 is
the one that gets updated.
| | 04:25 | So that WHERE clause is really
important when you're doing update.
| | 04:28 | It's actually pretty easy to
accidentally overwrite an entire table in
| | 04:33 | a database.
| | 04:35 | If you like, you can update more
than one column at a time in a row, and
| | 04:43 | that looks like this.
| | 04:46 | And now that fourth row, instead of
having a quote from The Terminator, it has
| | 04:50 | got Rosebud from Charles Foster Kane.
| | 04:52 | And then finally, if you want to delete
a row, it's as simple as changing this
| | 05:00 | UPDATE to saying DELETE FROM t WHERE id
=4, and then it will simply delete the
| | 05:07 | row entirely, and there, now that row is gone.
| | 05:11 | So that in a nutshell is just a quick
overview of how you insert data into a
| | 05:15 | table, how you update data in a table,
and how you delete data from a table.
| | Collapse this transcript |
| Reading data with SELECT| 00:00 | The SELECT statement is at the heart of
the SQL language and Postgres supports
| | 00:04 | all its various functions.
| | 00:05 | Let's take a look at the basics.
| | 00:07 | Here I've selected the album
database from the database selector over here,
| | 00:11 | and here's some SELECT statements operating
on the album table from the album database.
| | 00:17 | The first one you've seen, it's very common,
| | 00:19 | this is just select everything from the
table, so that will select the entire table.
| | 00:25 | The second one selects just
a few columns from the table.
| | 00:28 | And the third one is the same as the
second one, but it's actually giving us
| | 00:33 | human readable names for the various columns,
| | 00:36 | Artist, Album and Release Date.
| | 00:38 | And you'll notice that in Postgres you
need to put quotes around these if you
| | 00:42 | don't want it to fold everything to lowercase.
| | 00:44 | So even though there's no spaces within this
string, it still needs to have quote marks.
| | 00:49 | And you'll notice that there are
double quote marks, they are not the single
| | 00:51 | quote marks that represent an SQL
string, because this is not an SQL string,
| | 00:57 | this is something that's specifically for
the title of the column, for Postgres purposes.
| | 01:02 | So they're distinguishing that by using the
double quotes instead of the single quotes.
| | 01:06 | So when I go ahead and run this, see
our first one is the entire table, our
| | 01:10 | second one has just a few of
the columns from the table,
| | 01:14 | there's no Released column, and the
third one has the Artist, the Album, and
| | 01:19 | the Release Date. And you'll notice that those
titles for the column titles are all in uppercase.
| | 01:24 | So even with this second one, if I was
to make this one just all caps, ARTIST
| | 01:29 | like that, it would still work just
fine, and it simply folds all of those
| | 01:34 | column names down to lowercase.
| | 01:36 | So it doesn't care that I put it all in caps,
| | 01:39 | it's going to put it in lowercase anyway.
And the same happens when I define the table,
| | 01:43 | so the table is actually defined in
all lowercase. And this is handy.
| | 01:47 | It prevents errors creeping in based on
case mismatch, but if you really want to
| | 01:52 | have a title that has mixed case or has
spaces in it like this, then you need to
| | 01:56 | use those double quote marks.
| | 01:58 | Now, let's take a look at another
form of SELECT that is pretty common.
| | 02:03 | I'm going to switch over here to
the examples file from Chapter04.
| | 02:07 | I'm going to take this query, which
includes a subselect, and we're going to go
| | 02:13 | ahead and paste that in there.
| | 02:15 | So what this is going to do is it's
going to select all of the tracks from the
| | 02:20 | track table that are
included in albums by two artists.
| | 02:25 | And so you actually have to read it inside out.
| | 02:27 | You take it from the parentheses and
you read this part first, where it says
| | 02:31 | SELECT id FROM album.
| | 02:32 | That's the album table, WHERE artist=
Jimi Hendrix OR artist=Johnny Winter, and
| | 02:38 | that's going to return a list of album ids.
| | 02:42 | Then it's going to select from the
track table where album id is in that list.
| | 02:47 | So we're getting the album ids from
the album table and we're using that to
| | 02:53 | select particular rows from the track tables.
| | 02:56 | We'll go ahead and we'll hit Go
here and we'll look at what comes back.
| | 02:59 | And what comes back is all of the
tracks from one Johnny Winter album and
| | 03:04 | there's one Jimi Hendrix's album.
| | 03:07 | So if I were to just take this SELECT
by itself, and I'll just put that up here
| | 03:12 | and I'll put a semicolon after it and
we'll run it as a separate statement.
| | 03:15 | You see that we get this list of these
two ids, and those are the ids that are
| | 03:20 | used in this WHERE clause, WHERE
album_id IN, one of those, 11 and 16.
| | 03:25 | You see that we only have
rows from album_id 11 and 16.
| | 03:29 | So that's called a subselect.
| | 03:32 | Finally, we're going to look at a type
of a join that doesn't actually use the
| | 03:36 | JOIN keyword, and then in another
movie we're going to look at joins in a
| | 03:39 | little bit more detail.
| | 03:41 | So here we get the title of the album
from the album table, the title of the
| | 03:45 | track from the track table, and the
track number from the track table.
| | 03:48 | And the way this works is again we're
using this AS clause, but we're using it
| | 03:53 | in a slightly different way.
| | 03:54 | So here we're using it to label these
columns, which is how we've used it before.
| | 03:59 | We're also using it down here to
actually alias the table name, so FROM album AS
| | 04:04 | a and track AS t WHERE a.id so that
would be the same as if I had written out
| | 04:11 | album.id = t.album_id.
| | 04:16 | And so again, that's as if I had written
out track.album_id ORDER BY album title
| | 04:22 | and track track_number.
| | 04:24 | So having this SELECT FROM two
separate tables with a comma in between them
| | 04:30 | actually performs a JOIN, and
this was originally way back when,
| | 04:33 | this was originally the syntax for
JOINs, and sometime in the 1990s, SQL
| | 04:39 | evolved to include the JOIN keyword, and
we'll look at the JOIN keyword in another movie.
| | 04:44 | But for now what we have here is a very
simple JOIN, and when we run this we see
| | 04:49 | we have album titles and, again, that's
the title column from the album table,
| | 04:54 | track titles, that's the
title column from the track table;
| | 04:59 | and track_number, and there is all of our
tracks associated with their album names.
| | 05:06 | So that's a very simple JOIN that's
done entirely with the SELECT statement.
| | 05:10 | So like I said, the SELECT keyword is
probably the most common keyword in all of SQL.
| | 05:16 | It's really a workhorse and it
does a lot of different things.
| | 05:19 | These are the parts of SELECT
that you're likely to use the most.
| | Collapse this transcript |
| Reading with JOIN| 00:00 | The Join keyword is used as a clause
and select for retrieving data from two or
| | 00:03 | more tables in one query.
| | 00:06 | This can be a little bit
confusing, so let's look at an example.
| | 00:09 | I'm going to select the world database.
I am just going to go ahead and run this
| | 00:12 | query, and you'll see that I've got a
list of country names and languages that
| | 00:17 | are spoken in those countries, and this
is the query that produces that, and it
| | 00:21 | produces it from two different tables.
| | 00:23 | There is a countryLanguage table and
there is a country table, so let's take a
| | 00:27 | look at these two tables.
| | 00:29 | We're just going to look at the
first five rows from each of them, so the
| | 00:46 | countryLanguage table just has country
code and the language, whether or not
| | 00:50 | it's the official language and the percent
of the population that speaks that language.
| | 00:54 | The country table on the other hand
has a lot of stuff, but you'll notice
| | 00:58 | that it has a corresponding code and
it's got the name of the country, and these
| | 01:02 | are the things that we're interested in.
| | 01:05 | So when we look at this Join query
here, we see we are selecting From
| | 01:09 | countryLanguage As L, so that alias L
refers to that country language table.
| | 01:16 | So when you see l.language that's this
column here, the language column from
| | 01:21 | the countryLanguage table, and it's
joining the country table as C, so that's
| | 01:27 | this table down here.
| | 01:28 | On l.countryCode = c.code, so in others
words, in the countryLanguage table, it
| | 01:34 | looks at the countryCode, and then it
searches through the country table for
| | 01:39 | that same code in its code column,
and from there it gets the name.
| | 01:44 | And so the Join is actually pretty simple.
| | 01:47 | This is the important part right here.
| | 01:49 | So the Join happens on this condition,
and it works like this, for each row in
| | 01:54 | the country language table,
because that's our primary table there,
| | 01:57 | it looks for any rows in the country
table where this condition is true, and it
| | 02:02 | uses those rows in the results.
| | 02:05 | And so that's why in our results here
we have several rows where it's true for
| | 02:11 | the country of Afghanistan.
| | 02:12 | We have several rows where it is
true for the country of Albania, and for
| | 02:16 | those rows, it simply goes out of that country
table and gets the name and displays the name.
| | 02:21 | Let's go and simplify this example a
little bit by putting in a Where clause.
| | 02:26 | We can just put this in right here and
run it, and so now we're only going to
| | 02:33 | display the rows where the country
name is equal to the United States, and so
| | 02:38 | we can see there is a number of rows
in the countryLanguage table that have a
| | 02:42 | country code that is equal to the country
code for the United States in the country table.
| | 02:49 | Let's take a look at another example;
| | 02:52 | this one is from the album database.
| | 02:56 | So we'll select the album database and
we'll go ahead and paste this in and run it.
| | 03:01 | And what we're doing here is we're
displaying all of the tracks in the track
| | 03:05 | table along with the artist name and the album.
| | 03:09 | And it's getting the artist and the
album name from the album table, so
| | 03:13 | a.artist, a.title you see album As a.
And it's joining those on the condition
| | 03:19 | where the album_id is equal to the id
column in the album table, and that makes
| | 03:25 | it possible for us to display all of
the tracks in the track table along with
| | 03:29 | the artist name and the album name.
| | 03:32 | So this is where the relational
in relational database comes in.
| | 03:36 | These relations are typically
accomplished by using a sequential ID number in
| | 03:42 | each of these tables.
| | 03:43 | If you look at the album table, you see
that it has these unique ID numbers and
| | 03:55 | it's those ID numbers that make this possible.
| | 03:57 | If you look at the track table, you'll
see that there is an album ID column and
| | 04:02 | that corresponds to the ID column in
the album table and that makes these
| | 04:06 | relationships possible.
| | 04:08 | So that's a quick overview of the Join keyword.
| | 04:11 | We'll be using it some throughout
this course, so it's a good idea to
| | 04:14 | understand how it works.
| | Collapse this transcript |
|
|
5. SQL ExpressionsUnderstanding SQL expressions| 00:00 | In Postgres as in standard SQL an
expression may be used anywhere a value can be used.
| | 00:06 | For example, this SELECT statement
will return 1 value, 25, the result of the
| | 00:11 | expression 5 times 5, and the INSERT
statement will insert a row with the number
| | 00:16 | 4, a NULL value and the string
your name in all Lowercase letters.
| | 00:23 | Expressions use operators to operate on
values or the results of other expressions.
| | 00:28 | The Standard operators include the
string concatenation operator, arithmetic
| | 00:33 | operators, comparison
operators, and boolean operators.
| | 00:38 | Other special operators include IS
and IS NOT for comparisons, query result
| | 00:43 | matching operator is LIKE, ILIKE,
and SIMILAR TO for regular expressions,
| | 00:48 | BETWEEN for paired comparisons and IN
and NOT IN for testing inclusion in a
| | 00:54 | list or query result.
| | 00:57 | CASE is the conditional operator like IF
/THEN/ELSE in other languages and it's
| | 01:02 | also used like a ternary
conditional operator in C like languages.
| | Collapse this transcript |
| Matching patterns with LIKE| 00:00 | Postgres has a couple of extensions to the
standard SQL LIKE pattern matching operator.
| | 00:06 | For example, here using the world
database. If we SELECT FROM the City table
| | 00:11 | WHERE Name LIKE 'Z%' ORDER BY Name,
that's the standard usage of the SQL LIKE
| | 00:19 | operator and it will return rows
where the name column begins with capital
| | 00:24 | letter Z, and so we see there is a bunch of
cities that begin with the capital letter Z.
| | 00:31 | Now if I change this to a lowercase z and
run it again, you see we get no results.
| | 00:38 | And so in order to have this operator
be case insensitive, Postgres has the
| | 00:44 | extension ILIKE, beginning with a
letter I and that makes it case insensitive.
| | 00:49 | And now you see that we get the same
results, as when we have the capital Z
| | 00:54 | although we are using a lowercase z.
| | 00:58 | Likewise, if you want to define the
records where the second letter was a W, you
| | 01:03 | could use the underscore which matches
one letter in SQL and a w and in this
| | 01:09 | case I am using a lowercase w, but
because I'm using ILIKE, it doesn't matter,
| | 01:14 | either lowercase or
uppercase will work the same.
| | 01:16 | And the percent sign, again from
standard SQL, means match any number of any
| | 01:22 | kind of character to the end of the string.
| | 01:24 | And so if I press Go here, we get all of
the records where the second letter is a W.
| | 01:31 | But now what if I wanted the records
where the first letter was a K or a Z and
| | 01:37 | the second letter was a W?
| | 01:39 | There isn't really a way to do
that with the standard LIKE operator.
| | 01:42 | And so most database engines have
some sort of an extension to use regular
| | 01:47 | expressions or some other pattern
matching system, and in fact, Postgres
| | 01:52 | has SIMILAR TO which uses a kind of
regular expressions there, a SQLized
| | 02:00 | Regular Expression.
| | 02:01 | So instead of the dot character being
used to match a single character, the
| | 02:06 | underscore is as it is in SQL.
| | 02:09 | So this is just going to look a whole
lot different than it would in standard
| | 02:13 | POSIX regular expressions.
| | 02:14 | We are going to have a Z and a K here,
so it'll match either a Z or a K in
| | 02:19 | the first position.
| | 02:21 | And then we'll have the W, so that
second letter is always going to be the W.
| | 02:25 | And then we have to match anything after that.
| | 02:28 | So we use the undersore, instead of
we would use a dot in POSIX Regular
| | 02:32 | Expressions, and the plus sign.
| | 02:35 | And so this will get all the records
that begin with a K or a Z and have a W in
| | 02:40 | the second position.
| | 02:42 | So that's SIMILAR TO, and that is the
pattern matching expressions that are
| | 02:47 | used in Postgres.
| | Collapse this transcript |
| Choosing from multiple conditions with the CASE expression| 00:00 | The Case operator is used to select
between multiple conditions, for example if
| | 00:05 | we have this table here in the test
Database, we get a table with a couple
| | 00:10 | boolean values and I've inserted one
and a zero into them and when I run this
| | 00:15 | you see we get a 1 for true in
column a blank for false in column B. But
| | 00:22 | perhaps I want to display it differently and I
can use a case conditional operator for that.
| | 00:28 | So let's go over here to our Examples
file from Chapter 5 and I'm just going to
| | 00:33 | copy and paste this
SELECT statement in right here.
| | 00:39 | This has a couple of CASE conditionals in it.
| | 00:43 | The first column of this result, which
it says as boolA, so that's the first one.
| | 00:47 | It has this case which begins
there and ends with the end.
| | 00:51 | WHEN a THEN, it will return a TRUE else
it will return a FALSE. It's very simple.
| | 00:57 | We have got exactly the same one here
for b and when I run this you see our
| | 01:01 | second query as boolA and boolB and the first
one says TRUE and the second one says FALSE.
| | 01:08 | So case looks a little bit complicated,
but it's really very straightforward.
| | 01:12 | And it's used in cases where you need a
conditional. And in some ways some people
| | 01:17 | say it's like if then else in some
languages and it can be used that way.
| | 01:22 | It's more commonly used like this,
like for instance the ternary conditional
| | 01:27 | operator in the C language, where it
takes a condition and it will return either
| | 01:32 | one valuable or another value.
| | 01:34 | So that's the CASE statement in SQL,
and it's a standard case statement in SQL.
| | 01:39 | It's implemented in a
standard way here in Postgres.
| | Collapse this transcript |
| Using casts to force type| 00:00 | The purpose of a cast is to take a value
of one type and force it to another type.
| | 00:05 | This can come in handy at times, so
here's a simple explanation of how it works.
| | 00:10 | We start by creating a table and it has
an integer column and a real column, and
| | 00:14 | I put the same value into both of them.
| | 00:16 | A simple 9 digit number, and
then I select them from the table.
| | 00:20 | So when I look at it you see the
number on the left, the integer, has all 9
| | 00:23 | digits, the number on the right has 6
digits and one of them is rounded, you
| | 00:28 | notice that's 123457 and scientific
engineering notation showing that its
| | 00:35 | times ten to eight.
| | 00:36 | So let's go-ahead and cast that left
value into a real number and see what
| | 00:41 | happens. Take this cast string right here.
| | 00:44 | This is our 05_examples file from the
Chapter 5 folder in the exercise files.
| | 00:50 | And I am just going to paste it in and
after this select there, and we will run that.
| | 00:55 | And we see that now both of
numbers are rendering the same way.
| | 00:58 | So it's as if they were both type real.
| | 01:01 | So we have the syntax of the cast it
has a keyword CAST and then in parentheses
| | 01:06 | it has the value that you are
casting and the AS REAL as the new type.
| | 01:12 | So I can also do other types and I have
another example here where I am going to
| | 01:17 | cast both of them to numeric with a
fixed resolution, so there is numeric 15,2
| | 01:26 | which means that it should have 15
digits of significance with the decimal point
| | 01:30 | two places from the right.
| | 01:31 | And when I run this you will notice that
the number on the left comes out just fine.
| | 01:35 | It's got all 9 of its original digits
and a .00 exactly as we would expect it.
| | 01:40 | And the number on the right
is still rounded 123457000.000.
| | 01:46 | That's because it was originally stored
as a real and it lost its resolution, it
| | 01:52 | actually lost that day to those
significant digits, at the point when it was
| | 01:56 | stored as a real, and so taking it out
of real and casting it to something else
| | 02:00 | isn't going to recover lost data, but
it will get it to display in a certain
| | 02:05 | formatting and it will actually make it
into that type so that you can use it as
| | 02:08 | that type in an expression that
requires it to be of that type.
| | 02:13 | So that's how a cast works and this
is one those things that's pretty much
| | 02:16 | exactly how it is in the SQL standard
and so Postgres isn't doing anything
| | 02:21 | special with this and its just
using the standard SQL syntax.
| | Collapse this transcript |
|
|
6. Mathematical Functions and OperatorsUsing operators for simple arithmetic| 00:00 | Postgres provides a rich set of
mathematical operators and functions as you can
| | 00:04 | see here on this Documentation
page from the Postgres web site.
| | 00:09 | These are the mathematical operators that
are available, and there is also a rich
| | 00:13 | set of mathematical functions,
including all the common trigonometric
| | 00:18 | operations as well.
| | 00:20 | Let's take look at how we use
some of these operators in practice.
| | 00:24 | So I'll bring up SID here and for
example if you wanted to simply do simple
| | 00:30 | arithmetic, you can say
something like SELECT 5*30;
| | 00:32 | and you get a
result of 150 like it should be.
| | 00:38 | If you want to do a simple division
you can say something like SELECT 7/3
| | 00:43 | and the answer is 2,
because this is integer division.
| | 00:50 | Seven is an integer and three is an
integer, and so it's going to give you an
| | 00:54 | integer division, which
will ignore the remainder part.
| | 00:57 | If you use the modulus operator, which
is the percent sign, that will give you
| | 01:02 | the remainder which is one.
| | 01:04 | So 7/3 is 2 with a remainder of one.
| | 01:08 | If you really want the floating-point
division you can say 7.0/3; and this'll
| | 01:15 |
| | 01:15 | give you a double precision answer, 2.
33333 et cetera, because now 7.0 is a
| | 01:23 | literal floating-point number, which is
interpreted as being double precision.
| | 01:28 | Let's take a look at this a little bit
more in practice with an example from
| | 01:31 | our world database.
| | 01:33 | In the world database we
have a population column.
| | 01:36 | If I say Select name, population FROM
country, and we'll just take a look at
| | 01:45 | the first 10 rows of that query, and
we see that we have population, which is
| | 01:52 | in absolute numbers.
| | 01:54 | In other words if the population is
100 million, let's take a look at the
| | 01:58 | countries instead of limiting it to 10,
let's just say, where population > 100000000;
| | 02:08 | You see there are some
pretty big numbers in there.
| | 02:09 | So if we want to, just go
ahead and format this a little bit.
| | 02:15 | If we want to, we could instead look at
population/1000000; and let's also sort
| | 02:24 | this, ORDER BY population DESC;
| | 02:33 | And now we see that we have just the
countries with greater than 100 million
| | 02:37 | population, and the population in millions.
| | 02:40 | Of course, we can name
this column if we want to.
| | 02:43 | We can say As "Pop (MM)" and that gives us
exactly the result that we were looking for.
| | 02:51 | So that's one example of how we
could use math operators in practice.
| | 02:56 | Let's take a look at one more
example that has to do with prices.
| | 02:59 | This is actually even a little bit
more common. If we take a look in our test
| | 03:03 | database at the sale table,
| | 03:10 | You see that we have price in
hundreds of dollar and cents.
| | 03:14 | And we might want to actually
format that as dollars and cents instead.
| | 03:20 | So instead here, let's just look at the
item_id and the price, and we'll divide
| | 03:26 | the price by 100 so
that we get dollars instead of cents.
| | 03:29 | Now the problem is that we are
just getting the dollars part,
| | 03:36 | we are not getting the cents part,
because we did integer division again.
| | 03:40 | And now if we want the cents, we have
to do modulus, and then we would need to
| | 03:43 | concatenate it with a period, and
that's starting to get a little bit messy.
| | 03:47 | Instead, what we can do is we
can cast this price AS NUMERIC.
| | 03:56 | Because this number has a
fractional part, it's going to do that double
| | 04:00 | precision, arithmetic again.
| | 04:01 | And so now we get the dollars and cents,
but we also get a bunch of zeros at the end.
| | 04:07 | So there is one more thing we need to do
in order to format this just as we want it.
| | 04:11 | We can use the TO_CHAR function,
which is technically a string function.
| | 04:16 | It converts this number into a string, but
as it does it, it allows us to format it.
| | 04:22 | It's got this other operator which
is a format string where we can do
| | 04:26 | something like this.
| | 04:31 | And now we get exactly the result that we want.
| | 04:33 | And in fact, if some of these numbers
were larger, we'd even put the comma in
| | 04:36 | them for the thousands place.
| | 04:38 | So there you have an idea of how you
can use some simple arithmetic operators
| | 04:42 | in your SQL.
| | Collapse this transcript |
| Using math functions| 00:00 | Postgres provides a rich set of
mathematical operators and functions as you can
| | 00:04 | see here in the Postgres documentation.
| | 00:07 | We have the operators up there which we talk
about in a different movie in this chapter.
| | 00:12 | And here's a full list of the
mathematical functions that are available, quite a
| | 00:17 | few, very complete and
trigonometric functions as well.
| | 00:22 | We are going to take a look down a
couple of simple examples of how you can
| | 00:25 | use this in your SQL.
| | 00:27 | So bring up SID here and we will start
just by creating a table in the test database.
| | 00:32 | We get a couple of integers,
and we will insert some values.
| | 00:41 | 1 and 2 and 3 and 4, maybe with a couple
of negative values as well, -1 and -2, and
| | 01:06 | we will take look at them.
| | 01:13 | So there's the values that we've
inserted into our little table. And now if we
| | 01:17 | want to we can look at the absolute
value of these which means that even for the
| | 01:22 | negative values it will show
us their positive equivalence.
| | 01:26 | Look at the absolute value of a and just b by
itself and meant to make these 3 and 4, didn't I?
| | 01:32 | There we go.
| | 01:34 | And we see we have the absolute value
of 1 and 3 and the negative values of
| | 01:39 | 2 and 4 over there.
| | 01:41 | Now if we change these integers to
numerics, let's go ahead and copy and paste
| | 01:48 | that over here, and we will
change these values as well.
| | 01:51 | Let's say 123.456 and 456.789 and we
will make those negative over here and take
| | 02:11 | a look at that result.
| | 02:12 | There we have that.
| | 02:13 | Now let's say we want to round that
value, so if we say ROUND(a) and that uses
| | 02:21 | the rounding function and without a
second argument that will just round it to
| | 02:25 | the decimal point and so I
get 123 instead of 123.456.
| | 02:32 | If instead I want to round it to two
decimal places, I can say ,2 and it
| | 02:37 | rounds it to two decimal places and you
see that 5 gets rounded up because the
| | 02:42 | digit after it is a 6.
| | 02:44 | These are just a couple examples of
how simple it is to use these math
| | 02:48 | functions in your SQL.
| | 02:51 | And in the Examples file here for
Chapter 6, you notice we have the URL here,
| | 02:57 | where you can find that
documentation on the Postgres web site.
| | Collapse this transcript |
|
|
7. String Functions and OperatorsFinding the length of a string| 00:00 | Postgres has a lot of string related
functions and you can find a complete
| | 00:06 | exhaustive listing in reference here on
the Postgres web site in the Reference
| | 00:11 | Manual for string functions.
| | 00:14 | These are the major ones and then there
is a longer list of other ones available
| | 00:20 | and there is an even longer list of
types of conversions that you can do,
| | 00:25 | character set conversions and that
can be really, really convenient.
| | 00:29 | Especially, if you're migrating data
from another system, I found that very
| | 00:33 | convenient when I migrated data
from my SQL to Postgres recently.
| | 00:37 | We are going to do something a little
bit different in this movie, in that I'm
| | 00:41 | not going to be using SID,
| | 00:43 | I am going to be using pgAdmin III.
| | 00:45 | And the reason for this is I want to
work with some characters that are not
| | 00:49 | 8-bits and PHP is just not very good at
that and SID is not designed to handle that.
| | 00:55 | It takes all the input and it
escapes it and turns it into HTML entities.
| | 01:00 | So we're just going to go ahead and
use the Query Builder here in pgAdmin to
| | 01:05 | look at the string length functions.
| | 01:07 | So I've selected the test database and
I am going to press the SQL button here,
| | 01:11 | and I am just going to type something in
SELECT LENGTH and I am going to type in
| | 01:17 | a string, This string has 30 characters.
| | 01:24 | That is if I didn't make a typo.
| | 01:25 | And when I execute the query,
indeed the length is 30 characters.
| | 01:30 | Now you'll notice that I get exactly
the same result when I say CHAR_LENGTH
| | 01:36 | because in fact, LENGTH and CHAR_
LENGTH are the same in Postgres.
| | 01:41 | I can also say BIT_LENGTH and get
the number of bits which under some
| | 01:46 | circumstances may be useful.
| | 01:48 | What's even more useful is
the one that says OCTET_LENGTH.
| | 01:53 | Now for those of you not familiar, OCTET
is the technical term for an 8-bit byte
| | 01:58 | of data, and in fact, this string has 30 OCTETS.
| | 02:02 | But here's the interesting part.
| | 02:03 | We come here to Chapter 7, I am going to
open my Chapter 7 examples' file, and I
| | 02:09 | am going to just select this one here.
| | 02:11 | This has three Chinese characters which
if Google translator is correct, means I
| | 02:17 | am a rhinoceros and I am just going to
select the length of this, and you'll
| | 02:22 | notice that it's three characters long,
and so if I say CHAR_LENGTH, it is
| | 02:29 | indeed three characters long.
| | 02:31 | But now if I say OCTET_LENGTH you'll
notice that it actually takes nine octets
| | 02:39 | to describe this and that's
because these are not won by characters,
| | 02:43 | these are UTF characters representing
something that's way outside of that first
| | 02:49 | page of data in UTF.
| | 02:51 | And so we are using UTF-8 here and we
can actually store this in a table and we
| | 02:58 | can work with these characters and we
can get the length of these characters,
| | 03:02 | but if you're using PHP or another
language to interact with Postgres and you
| | 03:07 | need to know how much space this string
is going to take, you are going to want
| | 03:12 | to use OCTET_LENGTH instead of CHAR_
LENGTH to find out how big the string is,
| | 03:18 | because it certainly looks like three
characters and if you just say LENGTH by
| | 03:21 | itself without asking for the OCTET_
LENGTH, it will tell you that it's three
| | 03:25 | characters, but if you allocate memory
space for 3-bytes and you try to copy
| | 03:31 | this string into it, you're
going to have a memory leak.
| | 03:34 | So when I am using Postgres, I tend
to favor OCTET_LENGTH for checking
| | 03:40 | the length of strings.
| | Collapse this transcript |
| Concatenating strings| 00:00 | Postgres has a few standard
ways to concatenate strings.
| | 00:04 | The most common is using the
Concatenation Operator which is standard SQL,
| | 00:09 | actually most of this is standard SQL.
| | 00:12 | And so you can take two strings,
'This is a string' and use the SQL
| | 00:18 | Concatenation operator which for
people who are familiar with other languages
| | 00:22 | it might look strange.
| | 00:23 | It's two vertical bars which
means other things in other languages.
| | 00:27 | And I will type another strings 'This
is another string' and we are putting a
| | 00:32 | semicolon and these two strings get
concatenated and you notice that there is no
| | 00:37 | space in between, no nothing in between.
| | 00:40 | Of course instead of using the
Concatenation operator you can use the
| | 00:43 | Concatenation function and it's simply
spelled CONCAT and then instead of that
| | 00:49 | operator you use a comma and put
a closing parentheses at the end.
| | 00:54 | And the result is exactly the same.
| | 00:57 | Now if you want to take several
strings and have some kind of a separator in
| | 01:02 | between them, you can use concatenate
with separator which is spelled like this
| | 01:06 | CONCAT_WS and you put the separator at
the beginning as the first argument and
| | 01:13 | then a bunch of strings,
| | 01:14 | we will just put in one more here.
| | 01:19 | And when I run this, you'll notice
that they get concatenated, but that
| | 01:23 | separator is now used between them.
| | 01:26 | So this is just a few simple examples of
how Postgres uses standard SQL functions
| | 01:31 | for string concatenation.
| | Collapse this transcript |
| Returning parts of a string| 00:00 | Postgres has several standard
functions for operating on parts of the string.
| | 00:05 | For example, if I type in here SELECT
POSITION ('string' IN 'This is a string');
| | 00:16 | we will get the number of characters in
from the beginning where that substring
| | 00:21 | is found inside the larger
string and we see that it's 11.
| | 00:25 | Likewise, I can say SELECT SUBSTRING(
'This is a string' FROM 11); and so that
| | 00:35 | will start 11 characters in and give me
the rest of the string which we now know
| | 00:40 | is a string, that says string.
| | 00:43 | Or I can add to this I can say
FOR 3 and it will just give me three
| | 00:48 | characters starting out of 11.
| | 00:49 | So I will get just str.
| | 00:52 | So these are very standard functions.
| | 00:54 | These are common in all the major
database systems and they work pretty much the
| | 00:58 | same across the board.
| | Collapse this transcript |
| Replacing strings within strings| 00:00 | Postgres provides standard functions for
replacing strings within strings. For example
| | 00:06 | I can do this, SELECT REPLACE and I
give the source string ('This is a string'.
| | 00:14 | And then the part of the string
that's going to be replaced 'is a'); with the
| | 00:19 | replacement 'is not a' and when I
execute that I get this is not a string.
| | 00:27 | So that's probably most common way to do this.
| | 00:29 | There's another interesting function
called Overlay and while many large
| | 00:34 | database systems have something like this,
| | 00:36 | this is the particular Postgres way to do it.
| | 00:40 | I can say OVERLAY and let's just take
the word string here and replace it with
| | 00:45 | xxxxxx and say PLACING and I do not
want this comma here, 'string' FROM and I
| | 01:00 | happen to know that that's 11
characters in and so I am going to say Go and now
| | 01:05 | it says this is a string and so it's
taken this xxxx which is 11 characters in
| | 01:10 | and it's placed this string there.
| | 01:13 | So this is possibly another useful function.
| | 01:16 | It's something to have in your tool kit,
| | 01:17 | you probably won't use it as
often as the Replace function.
| | 01:20 | But its good to know that it's
there should you ever need it.
| | Collapse this transcript |
| Trimming and padding strings| 00:00 | Postgres provides some simple, common
effective functions for trimming strings.
| | 00:06 | For example, if we have a string like
this and maybe it's got a few spaces in
| | 00:12 | front of it and a few spaces at the end of it,
| | 00:19 | you'll notice that it displays on the
screen or without those spaces and this is
| | 00:24 | a really common problem. And it
presents a real challenge to Web programmers
| | 00:29 | because let's say somebody is entering
their e-mail address to login to your web
| | 00:34 | sites and they enter a couple of
spaces at the beginning of it or couple of
| | 00:38 | spaces at the end of it and they don't
notice it, because people don't think of
| | 00:42 | spaces is actually taking up space.
| | 00:45 | But when we take a look at this with
View Source here in the Firefox web
| | 00:49 | browser, I am going to use the Context
menu, I am right-clicking here and View
| | 00:53 | Page Source, you can also get it with Ctrl
+U on a PC or Command+U on a Mac, if
| | 00:59 | you're using Firefox.
| | 01:01 | We will look at this in the View
Source and if we come all the way down here
| | 01:04 | to the end, you see this is our results,
see the column had their and the cell value,
| | 01:10 | this is a string and you have got
to spaces on either slide of it.
| | 01:14 | But when you look at it in the
browser you don't see those spaces.
| | 01:17 | And so the problem is that the spaces
are actually there and if you search
| | 01:20 | through your database for somebody's
email address and they have typed it with a
| | 01:24 | space and it's in your database
without a space or even worst its in your
| | 01:28 | database with a space and
they typed it without a space,
| | 01:31 | you're not going to get a match.
| | 01:33 | And so that's what the Trim functions are for.
| | 01:35 | If you are trim all of your input before
you store it and you can do this easily
| | 01:39 | by using the SQL trim function as you
store the data and then again when you
| | 01:45 | compare the data, then you will fine.
| | 01:48 | And so if I say SELECT TRIM and put
the parenthesis on either slide of that,
| | 01:53 | we will say Go and you will notice
couple of things you notice it says btrim
| | 01:57 | because trim is effectively an alias
from the btrim which means both sides trim.
| | 02:02 | And when we go back to View Source and
we look at this string now, you'll notice
| | 02:08 | that it's completely trimmed.
| | 02:09 | It's got the spaces trimmed from
the beginning and from the end.
| | 02:13 | There are couple of other forms of this.
| | 02:14 | There is ltrim, which just trims the
left-hand side and we will look at this
| | 02:19 | again in View Source and we will see
that the left-hand side is trimmed and the
| | 02:22 | right-hand side still has its spaces
and of course there is rtrim, which does
| | 02:30 | the same thing but for the right-hand side.
| | 02:33 | So it's actually an important function
to trim your strings especially when you
| | 02:37 | are getting input from users along with
all of the other things that you do to
| | 02:40 | prevent SQL injection attacks and
Cross Site Scripting and things like that,
| | 02:45 | just want to throw trimming
the strings in to that toolbox.
| | Collapse this transcript |
| Converting numbers| 00:00 | Postgres provides a single very
powerful function for converting numerics to
| | 00:04 | strings and this is TO CHAR.
| | 00:06 | And you will notice here on this page,
there are a number of different forms of
| | 00:10 | it and all the way down at the bottom
here because it's so flexible and does so
| | 00:15 | many different things, you'll see the
different options and template characters
| | 00:19 | and template patterns for formatting
numbers and a number of examples as well.
| | 00:25 | So let's just take a look at a
couple very simple examples of this.
| | 00:28 | We will start with a number, and I will
just say go and you will see that it just
| | 00:38 | echoes the number back and of I want to
format this I can say city TO CHAR, and
| | 00:43 | take that number and put in a format string.
| | 00:47 | Make sure I have enough digit spaces and
you will notice the format is the number with
| | 00:58 | commas in place and enough
digits after the decimal point.
| | 01:02 | Now if I take out some use digits after
the decimal point you will notice that
| | 01:06 | it simply truncates them,
it's not actually rounding off.
| | 01:09 | There's another simple form of this
called TO HEX, type in TO_HEX and just put
| | 01:17 | in a little number there.
| | 01:18 | You will notice that I get a
hexadecimal number 15b3 and if I want that in
| | 01:23 | uppercase hexadecimal, I simply say
upper before it and put parenthesis around
| | 01:28 | that so that it gets fit in to the upper
function and we get an uppercase version of that.
| | 01:33 | So this is actually a very
powerful function and it has a lot of
| | 01:37 | options available to it.
| | 01:38 | That's the simple form of it and
the most common use of it is simply to
| | 01:43 | put commas or decimal points or
something in a number that doesn't have it
| | 01:46 | otherwise.
| | Collapse this transcript |
|
|
8. Date and Time Functions and OperatorsGetting the current time and date| 00:00 | Like most of the time and date
functions in Postgres, getting the current time
| | 00:03 | and date follows the SQL
standard closely. For example,
| | 00:07 | I can say SELECT CURRENT_TIMESTAMP and
I get the current time and date out to
| | 00:16 | many microseconds, or I can just say
GET CURRENT DATE and that will give me
| | 00:22 | just the current date, or I can get current
time, and that will get me the current time.
| | 00:29 | You will notice that the time and the
timestamp also give me this part here
| | 00:34 | which is the Time zone relative to UTC.
| | 00:37 | I am in California in the
summertime and so that's -7 hours.
| | 00:42 | If I want the be local time
without the time zone, I can say SELECT
| | 00:46 | LOCALTIMESTAMP and I get this localized
string without the times zone, or I can
| | 00:55 | just say GET LOCALTIME and that will give me
the time component without the date component.
| | 01:02 | So these are mostly used to
automatically timestamp a table, for example I'm
| | 01:07 | going to switch over here
to my Exercise examples file.
| | 01:12 | I am just going to grab a bunch of SQL
here your and paste it in and what we
| | 01:16 | have here is we are creating a table
and his table has an integer and it also
| | 01:21 | has these timestamps.
| | 01:23 | So a is a timestamp with time zone and
B is a timestamp without a time zone.
| | 01:28 | If it has the with time zone clause
that means it adds the time zone and it
| | 01:32 | doesn't have the with time zone clause
then it's the timestamp without a time zone.
| | 01:37 | And both of these have a default value
of current timestamp, with the default
| | 01:43 | key word and so when I insert
values into them but allow the default to
| | 01:47 | happen in other words I am not inserting a
value into the AMB columns watch what happens.
| | 01:53 | I get this C123 and I also get these
automatic timestamps and you will notice
| | 01:58 | because we have so much resolution here,
there is very slightly fractions of
| | 02:02 | a second difference.
| | 02:04 | The entire elapse time was 45 ms and
you can see that the difference between
| | 02:09 | this 50 and that 05 is in
fact 45 ms more or less.
| | 02:15 | So this is how the current timestamp is
typically used to automatically insert a
| | 02:21 | timestamp into a table cell.
| | Collapse this transcript |
| Specifying a specific time zone with AT TIME ZONE| 00:00 | Postgres supports the SQL standard AT
TIME ZONE construct for converting time
| | 00:05 | zones to other time zones.
| | 00:07 | For the purposes of this example I've
created this table which has two columns,
| | 00:11 | a and b. Column a has the With Time
Zone attribute and column b does not.
| | 00:16 | So it's being stored without the Time Zone.
| | 00:19 | And I'm inserting one row with
DEFAULT, DEFAULT, just so they both get
| | 00:23 | the current timestamp.
| | 00:24 | So when I run this, you'll see the
current timestamp comes up in exactly the
| | 00:28 | same timed down to the microsecond as
you can see there, except column a has the
| | 00:34 | Time Zone attribute, so it says, -07, which is the
current time zone where I'm on the West
| | 00:39 | Coast of the United States.
| | 00:40 | It's in the summer, so this is
Pacific Daylight Time, which is seven hours
| | 00:45 | before Greenwich Mean Time.
| | 00:46 | So let's go ahead and
experiment with the AT TIME ZONE clause.
| | 00:52 | So I'm going to say here SELECT a AT
TIME ZONE 'EDT', which is Eastern Daylight
| | 01:01 | Time.I'm going to say, AS a, because
otherwise Postgres doesn't know how to name
| | 01:06 | the column and it ends up
folding the two columns together.
| | 01:09 | And b AT TIME ZONE exactly
the same 'EDT' AS b FROM t.
| | 01:18 | So go ahead and run that, and now what
we get, you'll notice on the left-hand
| | 01:22 | side, it's giving us the time in the
Eastern Daylight Time Zone, in other words,
| | 01:27 | this is a time three hours later and
you notice that it does not have the time
| | 01:31 | zone attached to it.
| | 01:33 | For the column without a timestamp,
it does exactly the opposite thing.
| | 01:38 | It treats the time that was stored as
if it were at the target time zone and
| | 01:43 | displays what the time here is.
| | 01:46 | So the behavior of the same function
on two different values with and without
| | 01:50 | the Time Zone attribute
is actually the opposite.
| | 01:53 | If the value being operated on has the
time zone attribute, AT TIME ZONE will
| | 01:57 | give you the time in the time zone specified.
| | 02:01 | If the value does not have a time zone
attribute, AT TIME ZONE will give you
| | 02:06 | your local time assuming that the value
that does not have a time zone attribute
| | 02:12 | is in the target time.
| | 02:13 | I'm going to say this one more time
one other way because it's a little bit
| | 02:16 | confusing and I want to
make sure that you get it.
| | 02:19 | For the a value which has a time zone,
AT TIME ZONE 'EDT' tells us what time it
| | 02:24 | is in Eastern Time Zone.
| | 02:26 | For the b value that does not have a
time zone, AT TIME ZONE 'EDT' tells us what
| | 02:30 | our local time is if that time
in the stored value were in EDT.
| | 02:37 | So this one is actually three hours earlier.
| | 02:40 | This is our time here on the West Coast
assuming that it's 16:12 on the East Coast.
| | 02:45 | It would be 13:12 here on the West Coast.
| | 02:48 | This time is 19:12 assuming that
it's 16:12 here on the West Coast.
| | 02:53 | This is the time that
would be on the East Coast.
| | 02:56 | Let's just take a look
at a couple more examples.
| | 02:58 | I'm going to go ahead and take this same
example and I'm going to just change it
| | 03:02 | so that it says UTC instead of EDT.
| | 03:05 | That should work exactly the same
way except with a seven hour difference
| | 03:09 | instead of a three hour difference.
| | 03:11 | We'll say Go and so if it's 16:15
here then it is 23:15 in UTC, which is
| | 03:20 | correct, and if it's 16:15 in
UTC then it would be 9:15 here.
| | 03:26 | Starting to get a little more clear.
| | 03:28 | Let's do this one more time
with a slightly different form.
| | 03:30 | I'm going to copy this and paste
it down there on the next line.
| | 03:36 | I'm going to say, AT TIME ZONE and
instead of saying UTC I want to give it
| | 03:40 | Western European Time and I don't
happen to know the abbreviation for that.
| | 03:43 | I know that it's one hour after UTC.
| | 03:47 | So I'm going to say INTERVAL '+01:00'
like that, and I'm going to copy that,
| | 03:54 | I'm going to come over here, and I'm
going to replace UTC over here with
| | 03:57 | exactly the same thing.
| | 03:58 | So you can specify your time zone as
an interval relative to UTC instead of
| | 04:04 | giving it an abbreviation, and so I'm
going to say Go and now I should get an
| | 04:08 | eight hour difference.
| | 04:09 | So 16, that makes it just past midnight
the same day, 16:16 that's correct, and
| | 04:17 | if it were 16:16 in the UTC+
1 then it would be 8:16 here.
| | 04:24 | All right, so this is a very powerful
and flexible facility for actually doing
| | 04:30 | Time Zone arithmetic.
| | 04:31 | For example, if you have a web site
that's being accessed by people all over
| | 04:34 | the world and you're keeping your local
timestamps in UTC and you'd like it to
| | 04:39 | be able to display them in people's
local time zone then this creates an easy
| | 04:43 | way for you to do that.
| | 04:44 | Or if somebody uploads a file from a
particular time zone and you want to
| | 04:48 | convert that to another time zone.
| | 04:50 | This also gives you a facility for doing that.
| | 04:53 | So that's how we do Time Zone
arithmetic in Postgres with standard SQL.
| | Collapse this transcript |
| Performing date and time arithmetic| 00:00 | Postgres supports arithmetic on
dates and times, using standard
| | 00:04 | arithmetic operators.
| | 00:06 | For example, I can simply say SELECT
DATE, today's date, minus another date, and
| | 00:20 | it will give me the number of days in between.
| | 00:22 | Or instead of another date I can give
it an INTERVAL, say 6 days and it will
| | 00:32 | give me that other date, or I
can do all of this with TIMESTAMPS.
| | 00:39 | And so here I'm taking a date and time and I
am subtracting 6 days 3 hours and 15 minutes.
| | 00:44 | And it gives me a very specific date
and time that's exactly 6 days 3 hours
| | 00:49 | and 15 minutes earlier.
| | 00:51 | So this is a very convenient feature
and it allows us a lot of flexibility in
| | 00:57 | how we handle dates and times in our SQL code.
| | Collapse this transcript |
| Getting parts of a timestamp with EXTRACT| 00:00 | Postgres supports the EXTRACT function
for extracting parts of dates and times.
| | 00:05 | For example, we can say SELECT EXTRACT(HOUR
FROM TIMESTAMP and give it a timestamp, say.
| | 00:20 | And when I press Go, it says
date part, but it's the hour.
| | 00:24 | It says 15, which is the hour,
which is exactly what we asked for.
| | 00:28 | If instead I were to say MINUTE, we
get one for the MINUTE part or if I were
| | 00:35 | to just say MONTH, we get 10 for the Month part.
| | 00:39 | So this is very convenient.
| | 00:41 | In fact, we can even do this with intervals.
| | 00:45 | I can say EXTRACT( MONTH FROM INTERVAL,
and then give it an interval, 3 years 2
| | 00:55 | months, 1 hour, 15 minutes and 2 is the month.
| | 01:02 | So it extracted the months from the interval.
| | 01:06 | So this is a useful tool for helping
you to do math on different parts of your
| | 01:11 | dates and times and intervals.
| | Collapse this transcript |
|
|
9. PHP InterfacesChoosing an interface| 00:00 | PHP provides two distinct
interfaces for the Postgres database system.
| | 00:05 | The PDO interface PHP data objects use
a normalized interface that works very
| | 00:12 | similarly across the
different supported database systems.
| | 00:15 | Its PDO that allows me to write some of the
scripts so that they work in MySQL, they
| | 00:20 | work with SQL Light and they also work
with Postgres with very little changes.
| | 00:26 | PHP also supports a more native
Postgres interface called PGSQL that more
| | 00:32 | closely resembles the Postgres C library.
| | 00:36 | First let's talk about PDO. PDO
provides a common interface that's very similar
| | 00:41 | across different database engines.
| | 00:44 | This is the interface that's used most
often and therefore it's the interface
| | 00:48 | that gets most attention from developers.
| | 00:51 | It has a rich set of methods, so it's
possible to do just about anything you
| | 00:55 | will want to do with your code.
| | 00:57 | And it provides excellent performance,
even superior performance in some cases
| | 01:03 | over the native interface.
| | 01:05 | The PGSQL interface is the native
interface for the Postgres database engine.
| | 01:11 | It was written on top of the Postgres C
interface and is essentially just a wrapper.
| | 01:16 | So expect it to work the same as what's
documented in the Postgres documentation.
| | 01:21 | It's a bit more granular, so you
should be able to get all the functionality
| | 01:25 | that you may need from the Postgres library.
| | 01:28 | But it does not provide exception
handling, so you'll need to check for errors
| | 01:31 | manually in your code.
| | 01:33 | For my part I prefer the PDO interface
for most work and I will be showing you
| | 01:37 | both in this chapter.
| | Collapse this transcript |
| Using the PHP PostgreSQL extension interface| 00:00 | In this movie we are going to create a
PHP script that accesses the database
| | 00:04 | using the native Postgres interface.
| | 00:08 | So we will start by making a
working copy of the start.php.
| | 00:11 | I am in the Chap09 folder of the
Exercise Files and this is the copy of the
| | 00:15 | Exercise Files that we put in the
document root of the web server so that it's
| | 00:18 | accessible to web server.
| | 00:21 | I am going to go ahead and make a
working copy of start.php and I'm going to
| | 00:25 | name this example-pgsql.
| | 00:26 | We will open that in the Text Editor.
| | 00:33 | Before you continue, it's important that
you update the password in this file to
| | 00:37 | match the password you entered when
you created the sid user in PG admin.
| | 00:42 | You see here on line 14 I have foo.bar.
| | 00:45 | This is where you'll need to put a
password for your sid user exactly as you
| | 00:49 | entered it in PG admin.
| | 00:51 | And this is based on the sandbox code
that we used in the QuickStart chapter.
| | 00:56 | It's got a couple little differences.
| | 00:58 | Down here in init function you notice
that it doesn't connect to the database.
| | 01:03 | The main function doesn't have a lot of
the stuff that was there in the original
| | 01:07 | version of this and you also notice
that we are not calling in the BWPGSQL
| | 01:11 | library, because we are not going to use that.
| | 01:13 | We are going to be using the
native Postgres interface instead.
| | 01:18 | I am also going to delete this try
block here, because the native Postgres
| | 01:21 | interface does not support exceptions.
| | 01:25 | So we will just start by deleting
that and we are going to go ahead and
| | 01:29 | initialize some variables here.
| | 01:30 | tn is for the table name.
| | 01:33 | So that'll get this TABLE_NAME constant.
| | 01:35 | This is from these constants up here.
| | 01:37 | I am just going to scroll up a
little bit so you can see them.
| | 01:39 | We are going to use these
four constants right now.
| | 01:42 | dbname for the DATABASE.
| | 01:47 | user for the PGUSER and pass for the PGPASS.
| | 01:53 | Now we are going to use these
variables to connect to the database.
| | 01:56 | There is a function called pg_connect
and I am going to pass it a connection
| | 02:06 | string and the connection string
looks like this; host=localhost
| | 02:12 | dbname=, and we will use the $dbname
| | 02:18 | user=$user and password,
all spelled out, =$pass
| | 02:27 | Now because we don't have exceptions we have to
check for errors every time, so say or error,
| | 02:32 | I am going to put in a string and
concatenate that with the pg_last error.
| | 02:42 | That does not get the $, It's a function call.
| | 02:46 | The string will say Could not connect:.
| | 02:51 | Now that we've connected to the
database to go ahead and send a couple queries
| | 02:55 | to drop the table if it
exists and to create the table.
| | 02:59 | So we are going to use pg_query and
we give it the database handle we just
| | 03:04 | created and the query. And the query
looks like DROP TABLE IF EXISTS and the
| | 03:12 | TABLE_NAME which will be this variable that
will get expanded and we will use or error.
| | 03:18 | I am just going to copy and paste
from up above here and we'll just change the
| | 03:25 | string to say Query Failed.
| | 03:30 | Now it's important to note here
because we are in PHP that I'm using double
| | 03:34 | quotes wherever I am expanding a
variable inside the string and I am using
| | 03:38 | single quotes wherever I am not.
| | 03:39 | This is just a convention that I use.
| | 03:42 | I could double quotes everywhere, but
they say there's a slight performance
| | 03:46 | improvement which really doesn't
matter in this circumstance for using single
| | 03:50 | quotes and under some circumstances if
I was really worried about that I might
| | 03:55 | just refuse to use the double quotes at all.
| | 03:57 | In this case for convenience and for
clarity I am just doing it this way.
| | 04:01 | So I am going to go ahead and copy and
paste this whole query and just change
| | 04:07 | the query to say CREATE TABLE $tn
and give it the table definition.
| | 04:14 | We will have three columns a, b, and
c and these will all be text columns.
| | 04:19 | I'll go ahead and copy and paste
and we have our error message already.
| | 04:27 | Now that we have created our table we
will go ahead and send out a message
| | 04:30 | that says we've created our table
and the message will say the Table %s
| | 04:38 | created successfully.
| | 04:41 | I think I've got enough letters in there.
| | 04:43 | Now you will notice that my message
function uses %s like printf and that's
| | 04:48 | because if you look down here at the
message function later in the code, you see
| | 04:52 | that it uses variadic arguments just like printf.
| | 04:55 | In fact, it calls vsprintf.
| | 04:58 | So I like that it works just like printf.
| | 05:00 | I find that really convenient,
| | 05:01 | I do that a lot in my code.
| | 05:04 | Now we are going to create a prepared statement.
| | 05:06 | What a prepared statement does is it
allows you to use one bit of SQL and just
| | 05:11 | change the parameters and use it over and over again.
| | 05:14 | So because we are using the native
interface we can do this, we can also do
| | 05:17 | this in PDO, and this is actually a
real convenience and it can be a real
| | 05:20 | performance gain as well.
| | 05:23 | So we use pg_prepare to prepare the
statement and we give it the database
| | 05:29 | handle and we also give it a prepared
statement name whereas I call it a statement handle.
| | 05:35 | Then we are going to give it the SQL and
again I am using double quotes, because
| | 05:40 | I am going to expand the $tn variable
and this is INSERT INTO $tn VALUES and we
| | 05:48 | are going to give it the positions.
| | 05:50 | Now most SQL engines will use
question marks for the placeholders, for the
| | 05:55 | parameters, but this one does not.
| | 05:57 | This one requires number
parameters with dollars signs.
| | 05:59 | So $1, $2, $3, and fortunately PHP is
smart enough to not expand those and not
| | 06:07 | complain about those.
| | 06:08 | Just to leave those alone as long
as there aren't any variables that fit
| | 06:11 | those particular tokens.
| | 06:13 | Then I've got my or error again.
| | 06:15 | I am just going to copy and paste that
from above here and I am going to change
| | 06:19 | this to say Prepare failed.
| | 06:22 | Now once I've prepared the statement I
can go ahead and use it, and re-use it
| | 06:26 | with pg_execute and pg_execute gets the
database handle, the prepared statement
| | 06:34 | handle, and the parameters are put in an array.
| | 06:38 | So put the parameters in array.
| | 06:41 | We will have a, b, and c and
we say or error pg_last_error.
| | 06:51 | Now we can go ahead and make three
copies of that and just change the parameters
| | 06:55 | to 1, 2, and 3 and maybe we spell it out
for the next ones, one, two, and three.
| | 07:07 | So now what we've done here is we've
created this prepared statement to insert
| | 07:11 | into the table and we're calling that
same prepared statement three different
| | 07:15 | times with three different sets of parameters.
| | 07:18 | Now that we've done that we can go
ahead and read those rows back from the
| | 07:21 | table and display them on the screen,
and we are going to do that with pg_query
| | 07:26 | and the difference here with this pg_query is
that we are actually going to get results from it.
| | 07:31 | These earlier pg_queries up above where
we did the drop table, the create table,
| | 07:36 | those aren't select statements and
those don't return anything that we
| | 07:40 | necessarily want to use.
| | 07:41 | They return a status which we are
using to check for error, but in this case
| | 07:44 | we're going to use a results handle.
| | 07:46 | We are just going to call this result
and the query will look like this as the
| | 07:51 | db handle and it says Select * From $tn
| | 07:56 | It says very simple select statement.
| | 07:59 | I am going to go ahead and terminate
this with us semicolon because we have
| | 08:02 | this results handle in a separate
variable and I can test for that if not
| | 08:07 | result for my error.
| | 08:10 | error Query failed and give it the pg
_last error and now that we have our
| | 08:22 | results we can go ahead and display them.
| | 08:25 | The way this works is recall something
called pg_fetch and we are going to use
| | 08:29 | the associative array version of it,
and each time we call that with the
| | 08:33 | results handle, it will go out to the database
and it will get the next result from that query.
| | 08:37 | So we put this in a while loop.
| | 08:43 | Simply row = pg_fetch_assoc for the
associate array version and with ($result)
| | 08:53 | And that's our results handle.
| | 08:56 | Now inside of this loop we
simply display our results.
| | 09:00 | Use copy and paste again
and that looks like that.
| | 09:12 | Again, we are using our variadic
position parameters in message and we are
| | 09:18 | referencing the elements
in the associative array.
| | 09:20 | That's the associative array that we
got back from pg_fetch associate array
| | 09:25 | and it went into row there
| | 09:27 | so now we can now simply access those
columns with a, b, and c just as we define
| | 09:32 | them up here in the create table, a, b, and c.
| | 09:34 | So this is really quite
convenient to do it that way.
| | 09:38 | Now that we have successfully
inserted the rows into the table and read the
| | 09:42 | rows back from the table, we can go ahead and
drop the table and we do that with a drop table.
| | 09:47 | I am just going to copy this one up
there and copy down it here and just say
| | 09:53 | drop table $tn and this line is
actually short enough and I can put all
| | 09:58 | of that on one line and just give the
message that we've dropped the table.
| | 10:02 | So I am saving by pressing Command+S
on this Mac here, and let's just take a
| | 10:17 | look at what we have done.
| | 10:17 | We connected the database up here on
line 32 and on line 35 we dropped table if
| | 10:24 | exists using the pg_query and we used pg_
query again to create table and each of
| | 10:31 | these queries actually each time we go
out and do something with the database we
| | 10:35 | are checking for the error and that's
because this database interface does not
| | 10:39 | have exception handling.
| | 10:41 | Then we prepare a statement to insert
into the table, and then we execute that
| | 10:46 | prepared statement three times with
different parameters to insert rows into the table.
| | 10:51 | Finally, we have this select statement
to read the rows back from the table and
| | 10:56 | a while loop to fetch those rows and
display them, and then we drop the table
| | 11:02 | down here at the end.
| | 11:04 | So let's go ahead and fire up the
browser and take a look at this in action.
| | 11:07 | Going out to my Exercise Files and Chap9
and example pgsql.php and there we have it.
| | 11:15 | It works perfectly.
| | 11:17 | Table test created successfully.
| | 11:19 | I have three rows of data a, b, c, 1, 2,
3, and one, two, three spelled out,
| | 11:23 | and table test dropped.
| | 11:25 | So that is how you use the Postgres
database engine within PHP using the
| | 11:30 | native Postgres interface.
| | Collapse this transcript |
| Using the PDO interface| 00:00 | In this movie, we're going to create a
database application using the PHP PDO
| | 00:04 | interface to access the
Postgres Database Engine.
| | 00:08 | We're going to start by making a
working copy of the start.php file here in the
| | 00:12 | Chapter 09 folder of the Exercise Files.
| | 00:15 | This is the copy of the Exercise Files
that's in the web server document root,
| | 00:20 | in the htdocs folder here
under the XAMPP directory.
| | 00:24 | That's so that this is accessible to
the web server, so that we can write our
| | 00:28 | code and then test it on the web server.
| | 00:30 | So I am going to go ahead and make
a working copy here of start.php.
| | 00:34 | I am going to name it example-PDO.php.
| | 00:39 | I'll open that in the Text Editor.
| | 00:42 | Before you continue, it's important that
you update the password in this file to
| | 00:45 | match the password you entered when
you created the SID user in pgAdmin.
| | 00:50 | You see here on line 14, I have foo.bar.
| | 00:53 | This is where you'll need to put the
password for your SID user, exactly as you
| | 00:57 | entered it in pgAdmin.
| | 01:00 | And this is a modified version of the
sandbox code that we used in the Quick
| | 01:05 | Start chapter, and you notice that
this version does not call in the BWPG SQL
| | 01:12 | Library, because we're not going to be using
that, we're going to be using PDO directly.
| | 01:16 | And you also notice that the init
function is paired down, because we're not
| | 01:20 | creating the database or
anything in the init function.
| | 01:22 | So we're going to start here in main
and we're going to start right here under
| | 01:26 | this $tn = TABLE_NAME;
| | 01:29 | and we're going to go ahead and
create a PDO object, $db = new PDO.
| | 01:38 | I'm going to start with the connection
string here, which looks like this, pgsql.
| | 01:43 | That specifies the database engine,
and a colon, and host=localhost;
| | 01:50 | and port=5432 and dbname= and here
we're going to use the string concatenation
| | 02:00 | operator to bring in the DATABASE
constant, because that is defined as a
| | 02:04 | constant up here above, where these other
constants are that we're also going to use.
| | 02:09 | We're going to use the PGUSER
constant, and the PGPASS constant.
| | 02:14 | I am just going to bring
this down to the next line.
| | 02:16 | I will put in the semicolon right there,
because we will be using that, and I am
| | 02:21 | going to pass it an array,
and this is a parameter array.
| | 02:24 | You can put all kinds of stuff in here
if you want to, but all I am going to put
| | 02:27 | in here right now is PDO::ATTR_
PERSISTENT, and it's that one right there, and I
| | 02:37 | am going to give it a value of true.
| | 02:39 | Now, what this does is it tells the
PDO driver it's okay to keep persistent
| | 02:45 | connections open, and that allows if
you have a web server that's handling a
| | 02:50 | few connections at a time, it
doesn't need to set up a connection to the
| | 02:53 | database every time, it can keep those
connections alive if this code is going
| | 02:58 | to be used over and over again
in those web server instances.
| | 03:02 | And so this is actually a good performance
enhancement and it doesn't really cost much.
| | 03:07 | So I go ahead and use it pretty much every time.
| | 03:09 | Now, I am going to go ahead and set
another attribute, and I am going to do this
| | 03:13 | using the set attribute method,
just so you see how this is done.
| | 03:19 | And this one is PDO::ATTR_ERRMODE.
| | 03:24 | It's that one right there.
| | 03:28 | And I am going to set it to
this value PDO::ERRMODE_EXCEPTION.
| | 03:36 | So that sets the error mode, so that it
throws exceptions when there's an error.
| | 03:39 | That way we don't need to be checking
for errors with every call, instead we can
| | 03:43 | just put all of our database code in
this try block and catch the error once in
| | 03:49 | the catch side of the try block.
| | 03:50 | So now let's go ahead and execute
some SQL, db-exec, and I am going to use
| | 03:59 | double quotes because I am
going to be expanding a string.
| | 04:03 | I am going to be using this TABLE_NAME
string that's defined up here, and it's
| | 04:07 | DROP TABLE IF EXISTS $tn
| | 04:10 | I am just going to go
ahead and copy and paste here.
| | 04:15 | Now I am going to create the table,
CREATE TABLE $tn and three columns, a, b,
| | 04:25 | and c and these are going to be TEXT columns.
| | 04:32 | So now we've created our table.
| | 04:33 | So we'll send out a message that
says that, message('Table %s created
| | 04:45 | successfully'), and we're going to
give it that tn here, and so it will
| | 04:49 | display the table name.
| | 04:51 | Now we have created the table, and
let's go ahead and populate it with a
| | 04:55 | little bit of data.
| | 04:56 | PDO allows us to use prepared statements,
so we can take a statement handle, and
| | 05:02 | say db->prepare, and then
give it some SQL in here.
| | 05:08 | We'll say INSERT INTO $tn VALUES, and
give it placeholders, question mark,
| | 05:16 | question mark, and question
mark for the placeholders.
| | 05:20 | And then we can execute that statement
with the Statement Handle, execute, and
| | 05:26 | we're going to do this three
times with different data in it.
| | 05:30 | We pass it an array with the
parameters, and this array will have three
| | 05:34 | elements to correspond with the three
question marks, and it will place those
| | 05:38 | parameters inside the SQL statement,
and it will do escaping and make sure that
| | 05:43 | it's safe when it does that.
| | 05:44 | So this is really the best way to
put parameters in an SQL statement;
| | 05:50 | a, b, and c, and then we're going to
do that three different times here, and
| | 05:57 | just change the values; 1, 2, 3, and
we'll spell out; one, and two, and three.
| | 06:07 | So now we've populated the
database, that was easy.
| | 06:12 | Now, let's read that data back.
| | 06:14 | $sth = db->prepare.
| | 06:18 | So we're preparing another
statement here; SELECT * FROM $tn.
| | 06:26 | And then we set the Fetchmode for
fetching the data, and we're going to fetch in
| | 06:32 | an associative array.
| | 06:33 | So $sth->setFetchmode(PDO::FETCH_ASSOC).
| | 06:48 | And then we execute like that.
| | 06:54 | And we don't have any parameters, so we
don't have to pass any parameters to it.
| | 06:58 | And then we can take that statement
handle and we can actually iterate through
| | 07:03 | it as an iterator with foreach.
| | 07:04 | Now, what this does is that goes ahead
and executes the fetches, row by row,
| | 07:18 | putting each row into the row
variable as an associative array.
| | 07:23 | So now I can display those rows using
message(%s, %s, %s) for the three strings,
| | 07:33 | and then reference the associative
array elements, go ahead and copy and paste
| | 07:40 | this, and just change these.
| | 07:44 | So these associative array keys
correspond to the column names a, b, and c up in
| | 07:51 | the CREATE TABLE statement,
and that's all there is to it.
| | 07:54 | Now we have fetched those rows from
the table that we populated and we've
| | 07:59 | displayed them, so now all that's
left to do is to drop the table and exit.
| | 08:03 | So $db->exec("DROP TABLE $tn") and
message('Table<i>%s</i> dropped), and save
| | 08:25 | that, pressing Command+S on my Mac here.
| | 08:28 | Looks like I forgot a comma up there.
| | 08:31 | I do also take this
opportunity to do a little proofreading.
| | 08:34 | We've created a new PDO object to
connect to the database and we passed it the
| | 08:39 | PGSQL as the type of database, and
that tells PDO that this is a Postgres
| | 08:45 | database that it's connecting to.
| | 08:46 | We give it the host name, the port number,
and the database name, and a user and
| | 08:51 | password, and we set the persistent
flag using that associative array there.
| | 08:56 | We also set another attribute for error
mode to exception, and then we execute a
| | 09:02 | couple of SQL commands here;
| | 09:04 | DROP TABLE IF EXISTS and CREATE TABLE, to
make sure we're creating a fresh table each time.
| | 09:09 | And then we insert three rows;
| | 09:11 | a, b, c, 1, 2, 3, and one, two,
three spelled out, and we do that with a
| | 09:15 | prepared statement, and then we use
another prepared statement to fetch those
| | 09:19 | rows from the database. All right,
| | 09:21 | so I am going to press Command+S again
to make sure that I've saved this, and
| | 09:25 | let's go ahead and fire it up in the browser.
| | 09:28 | So navigating over to Chapter09, and we'll
click on example-PDO.php, and there we have it.
| | 09:36 | So this is working perfectly,
| | 09:37 | it creates the table, it populates the table,
it reads the table, and it drops the table.
| | 09:44 | So there we have it.
| | 09:45 | That's how we use the PDO interface
in PHP to access a Postgres database.
| | Collapse this transcript |
| Creating a library| 00:00 | Here I have opened the lib or library
folder in the Exercise folders and this
| | 00:06 | the copy of the Exercise folders that's
in the web server document root in the
| | 00:10 | htdocs folder and in this
directory there are two files.
| | 00:14 | One of them is the bwPgSQL.php.
| | 00:19 | This is the database wrapper library
that I created and this is actually the
| | 00:24 | library that I use most of the time
when I'm creating applications in PHP
| | 00:28 | that use a database.
| | 00:29 | Then there is the test-bwPgSQL, which is a
simple test suite for testing the library.
| | 00:36 | So what we're going to do we're going
to take a look at this and we're going to
| | 00:39 | talk about why I do this, why I think
it's important to do this and what makes
| | 00:44 | this a powerful choice
for creating applications.
| | 00:47 | So we'll go ahead and open the
library in the text editor here, and let's
| | 00:52 | just start by taking a
little tour of the library.
| | 00:54 | I call this a database CRUD wrapper.
| | 00:57 | What that means is that it's a
wrapper, because it basically wraps around
| | 01:01 | the existing interface.
| | 01:03 | The library itself uses the PDO
interface to access the database, but it
| | 01:07 | exposes a more friendly interface to
the programmer and in some cases it might
| | 01:12 | be specifically tailored to a given application .
I'll often customize this for a given application.
| | 01:18 | And I call it a CRUD library, because it
also has some convenience functions for
| | 01:24 | dealing with, creating, retrieving,
updating, and deleting records in a database.
| | 01:29 | At that level I tend to call them
records as opposed to rows, even though they
| | 01:32 | actually are rows, but it's just a
slight paradigm shift in how I think about
| | 01:37 | data in different circumstances.
| | 01:40 | So this is an object-oriented library.
| | 01:43 | So everything is in a class called
the bwPgSQL, and you instantiate it by
| | 01:49 | saying new bwPgSQL.
| | 01:52 | You'll notice that each of the methods
inside of this class, I've a little comment
| | 01:56 | at the top of them that just gives a
little usage so that I can look at this
| | 02:00 | later and I can say, what
is this and how do I use it.
| | 02:04 | So this is the constructor and
this does a little bit of work here.
| | 02:07 | It actually instantiates a PDO object and
it saves it in this private variable dbh.
| | 02:14 | The this syntax in PHP is for saving things
in private local variables inside of a class.
| | 02:21 | If you've looked at the PDO movie in
this chapter you see that this is very,
| | 02:25 | very familiar how this works, and then
I set the CLIENT-ENCODING to latin1 and
| | 02:30 | I've this long comment here about how
PHP doesn't play nice with Unicode and
| | 02:35 | even UTF-8 is problematic.
| | 02:37 | In the very latest versions of PHP
especially the beta version that's out now as
| | 02:42 | we're recording this, they're really
trying to change that, they are really
| | 02:45 | trying to fix how they use Unicode and
perhaps by the time you're viewing this,
| | 02:50 | this won't be true anymore.
| | 02:51 | But I've been using this library for a
long time and every once in a while I
| | 02:55 | go back and revisit how PHP does
Unicode, and I still haven't been satisfied
| | 02:59 | with it at this point.
| | 03:01 | And if this has been called with a table
name, and you notice the table_name has a
| | 03:05 | default value of NULL.
| | 03:06 | So it's an optional parameter.
| | 03:08 | If it's been called with a table_name
then the table_name also gets stored in
| | 03:12 | the private data up here and
that's used for the CRUD applications.
| | 03:18 | We have setter/getters for all these parameters;
| | 03:20 | for the table_name, for the database
handle, and for statement handle, and then
| | 03:24 | we have some of the workhorse function.
| | 03:26 | So sql_do--this gets used a lot.
| | 03:29 | This performs SQL without a return result set.
| | 03:33 | Instead it returns a count of the
number of rows that have been affected, and
| | 03:36 | again, this is very simple.
| | 03:38 | It's really just a
wrapper for prepare and execute.
| | 03:41 | For doing multiple queries and again without a
result set, it just calls the PDO exec method.
| | 03:49 | sql_query actually returns a statement handle
that allows you to iterate through a result.
| | 03:56 | So this allows you to do something
like a foreach loop, an iterator loop that
| | 04:00 | actually has the SQL right there
in the sql_query call, and so that's
| | 04:05 | actually really convenient.
| | 04:07 | In fact, let's just take a moment and
divert and take a look at that in action here.
| | 04:11 | This is the test suite, and you'll
notice down here I have a foreach loop with
| | 04:17 | the query right in the foreach loop.
| | 04:19 | So I don't have to have intermediate variables.
| | 04:20 | It's just very quick and easy and it just works.
| | 04:23 | We can switch back to the library now.
| | 04:27 | Then there is the dangerous fetchAll.
| | 04:29 | I recommend that you absolutely never do this.
| | 04:32 | I include it in the library just for
completeness, but I've never used it and
| | 04:35 | I don't recommend it.
| | 04:36 | It basically reads all of the rows of your
query into memory and then returns an array.
| | 04:42 | There might be some circumstances where
that would make sense, but most of the
| | 04:45 | time that's just a really, really bad idea.
| | 04:47 | Here is query_row for getting one row at a time.
| | 04:50 | There is some circumstances where that
be might be handy as opposed to iterating
| | 04:54 | through these Statement handle.
| | 04:55 | I like iterating through the Statement handle.
| | 04:57 | query_value for getting one value, and
again these are all very simple methods.
| | 05:02 | We're going to get into some of the
more complicated stuff here in a minute as
| | 05:05 | we get into the CRUD, and then we have
begin_transaction and commit, and again,
| | 05:09 | those are just wrappers for the
same named methods in the PDO Library.
| | 05:13 | Now we get into the CRUD stuff.
| | 05:15 | get_rec returns an
associative array based on an id.
| | 05:19 | So the way that I have the CRUD
methods set up is it requires a column named
| | 05:24 | ID that is a unique identifier, and
you pass that and when it creates a new
| | 05:30 | record in returns that and that's kind
of the handle for a particular record
| | 05:35 | or row in the table.
| | 05:37 | So get_rec takes an ID and it returns a row.
| | 05:42 | get_recs returns an iterable statement
handle for basically all the rows in the table.
| | 05:49 | It does SELECT with an asterisk.
| | 05:52 | Insert inserts a new record
and it uses an associative array.
| | 05:58 | So you pass an associative array where
the keys in the associative array are
| | 06:03 | named the same as the columns in the table.
| | 06:06 | You notice that it does a little bit of
magic here to create the PHP insert, and
| | 06:12 | in this case it uses the RETURNING
clause which is specific to Postgres, and
| | 06:17 | that gives it the ID that it can return.
| | 06:20 | Update again it goes through a little
bit of rigmarole to create the SQL and
| | 06:26 | this updates a record.
| | 06:27 | So you pass it the ID of the record and
an associate array with just the columns
| | 06:32 | that you want to update and it creates
a custom SQL statement that just updates
| | 06:38 | those particular columns.
| | 06:39 | So you don't actually have
to pass it the entire row,
| | 06:43 | you just pass it the columns are
changing, and that's a convenience.
| | 06:47 | Delete is very simple. You pass an id.
| | 06:49 | It deletes the row.
| | 06:51 | get_next is again just a convenience
method for getting the next record, and
| | 06:57 | then we get down into some
of the little utility methods.
| | 07:00 | Count_recs just does SELECT COUNT.
| | 07:03 | This is not a terribly efficient
thing to do in Postgres, a lot of database
| | 07:07 | engines like MySQL and SQLite,
they have optimizations for this case.
| | 07:13 | Because Postgres is so asynchronous and
object-oriented there is really no way
| | 07:17 | to do this efficiently.
| | 07:19 | According to Postgres documentation
they just don't see this as a priority.
| | 07:22 | They would rather have the
database be fast than convenient.
| | 07:26 | So that's a design choice and again
for data sets that are less than a few
| | 07:31 | thousand records this shouldn't be a problem.
| | 07:33 | But if you start getting these in
very busy databases, busy web sites, busy
| | 07:37 | database applications, you probably don't
want to be calling COUNT * very often anyway.
| | 07:43 | last_insert_id, this is really just
for compatibility with other database
| | 07:49 | engines like MySQL and SQLite
who have something like this.
| | 07:53 | In Postgres the serial columns like
Serial Primary Key that automatically
| | 07:58 | increments, what it does is it
actually sets up a sequence generator, and it
| | 08:03 | creates an alias and sequence
generator within your table.
| | 08:06 | So what this function has to do is
it needs to go out there and grab the
| | 08:09 | current value from the sequence
generator and it approximates that methodology
| | 08:14 | and you'll see in my CRUD application that I
actually use something like this for compatibility.
| | 08:19 | table_exists can be convenient sometimes
for checking if a table actually exists
| | 08:24 | and this queries the pg_tables view
that comes as part of the default template
| | 08:29 | for databases in Postgres.
| | 08:31 | Version does two different things.
| | 08:33 | It will either return the version of
this library or the version of the Postgres
| | 08:37 | database engine that's being connected to.
| | 08:40 | These timer functions are
useful for benchmarking.
| | 08:44 | So what we have here is a test suite and
every time I write a library like this,
| | 08:49 | I will always create a corresponding
test suite that goes through and exercises
| | 08:52 | each of the different methods,
and that's what this does.
| | 08:55 | Let me scroll through a little
bit slower so that you can see it.
| | 08:57 | Here we've some constant definitions at the top.
| | 09:00 | This is really based on my Sandbox code
that you've seen elsewhere in this course.
| | 09:04 | We see here, we connect to the
database by creating a new bwPgSQL object.
| | 09:10 | Create a table, insert some rows into
the table, and that's exercising the
| | 09:14 | sql_do method right there.
| | 09:17 | Then we exercise the table_exists
method by trying to test for a table that
| | 09:23 | doesn't exist and testing
for a table that does exist.
| | 09:26 | Here we test sql_query, here we test
sql_query_row, and now we start getting
| | 09:33 | into the CRUD methods.
| | 09:35 | There is get_rec, there is get_recs,
there is insert, there is update, and
| | 09:43 | delete, and then we DROP the TABLE.
| | 09:46 | Before you test this in your browser,
it's important that you update the
| | 09:48 | password in this file to match the
password you entered when you created the
| | 09:52 | SID user in pgAdmin.
| | 09:54 | You see here on line 17 I have foo.bar.
| | 09:58 | This is where you'll need to put the
password for your SID user exactly as you
| | 10:02 | entered it in pgAdmin.
| | 10:04 | So let's take a look at this in the browser.
| | 10:06 | We'll go ahead and open the browser and
navigate to the lib folder and there is
| | 10:12 | the test-bwPgSQL.php. Here we have it.
| | 10:16 | Again, this is very much like my
Sandbox code that you have seen elsewhere in
| | 10:20 | this course and effectively I'm just testing
all of the different methods in the library.
| | 10:25 | I'm displaying all of the
versions at the top here.
| | 10:27 | This is the version of the test suite here.
| | 10:30 | This is the version of the library.
| | 10:32 | Here's the version of Postgres.
| | 10:33 | And I'm timing things,
testing for table that does exist
| | 10:38 | takes 6 milliseconds testing
for a table that doesn't exist
| | 10:40 | takes 1 millisecond.
| | 10:42 | So these things make sense.
| | 10:43 | I'm writing a library.
| | 10:44 | I'm experimenting with the library,
and I want to test it as I do that, and
| | 10:49 | that's really all that this is about.
| | 10:51 | So we can see that
everything is executing just fine.
| | 10:54 | The whole elapsed time set is 100 milliseconds.
| | 10:58 | So I hope you can see the value
of creating your own libraries.
| | 11:00 | You're welcome to use my library,
you're welcome to modify it, and you're
| | 11:04 | welcome to use it for your own purposes.
| | 11:06 | Do with it whatever you like.
| | 11:07 | It's a great convenience.
| | 11:09 | It allows the code to be more
streamlined for you to pay more attention to the
| | 11:13 | application that you're building rather
than exactly how does the database work.
| | Collapse this transcript |
|
|
10. Web ApplicationsDefining CRUD| 00:00 | In this chapter we're going to look at
a couple of different ways to do CRUD -
| | 00:03 | Create, Read, Update, Delete.
| | 00:05 | These are the basic four
functions of any database application.
| | 00:08 | In this movie we're looking at the
CRUD application that we loaded up when we
| | 00:12 | initially installed the database,
and you notice I've created another
| | 00:15 | shortcut on my toolbar here.
| | 00:17 | We didn't do that when we first
installed, but there it is, and you notice that
| | 00:21 | this is just a localhost/CRUD/crud.php,
and I just drag that down in my toolbar
| | 00:29 | to create that button.
| | 00:31 | So this is a basic CRUD application.
| | 00:33 | It is a web application;
| | 00:34 | it's running in a web browser.
| | 00:36 | We'll look at the code in another movie,
for right now we're just going to take
| | 00:40 | a tour through the application itself.
| | 00:42 | This keeps a database of albums and the
tracks in the albums, and so it's really
| | 00:46 | managing two different tables.
| | 00:48 | It has got Album Table and a Tracks
Table and these are joined together.
| | 00:53 | For example, if I want to click on one
of these Albums down here, let's click on
| | 00:58 | Birds of Fire by the
Mahavishnu Orchestra, great album.
| | 01:01 | And you notice that we have the album
data up at the top and now at the bottom
| | 01:05 | we've got the tracks, and the tracks
are in order of track number and they have
| | 01:08 | a title and they have a duration.
| | 01:10 | So rather than edit these, let's go
ahead and create our own album here.
| | 01:14 | I'm going to just name this one Foo,
and the Artist will be Bar and the
| | 01:19 | Label will be Baz, and we'll just put in our
Released Date here, and I'll click Add Album.
| | 01:27 | And it immediately takes me to the Edit
Album screen, where I can start adding tracks.
| | 01:31 | And you'll notice that the cursor is
already positioned down there on the
| | 01:35 | first field of the tracks, where I can
enter a Track number and a Title and a Duration.
| | 01:40 | Now the Duration, I can enter it in
number of seconds, or I can do it in
| | 01:45 | minutes and seconds.
| | 01:47 | And when I press the Add button, it
adds that track down below, and it puts the
| | 01:51 | cursor in position to add the
second track, so I'll just do that.
| | 01:53 | I'm going to enter this one in
number of seconds, and you'll see that
| | 01:58 | it converts for me.
| | 02:00 | So there is Track 2 below Track 1.
| | 02:01 | Let's just add another couple of tracks here.
| | 02:09 | If I want to, I can edit these tracks.
| | 02:11 | I can come down here to Track #3 and I
can change the Duration say to 3:45 and
| | 02:18 | click Update and it
updates that in the database.
| | 02:21 | Or if I want to I can Delete a track,
ask me to confirm, and there now that
| | 02:27 | track is gone, and I can add it back in if I
want to, and there Track 3 is now called 5.
| | 02:36 | And you'll also notice that if I come
over here to the SID application, I'm
| | 02:40 | going to hold down the Command key while
I click on this, and it will open it in
| | 02:42 | another tab, and I select the album Database.
| | 02:47 | I can just type in select * from album,
and there is a list of all the albums.
| | 02:53 | And you'll notice that the last one
there is called Foo, the artist Bar, and
| | 02:57 | label Baz, and there is the release date.
| | 02:59 | And if I want to see the tracks for it,
I can just say select * from track where
| | 03:04 | id =, and maybe I don't know the id.
| | 03:07 | Well, I see the id over here, and
that's actually album_id, but instead I can
| | 03:12 | give it a subselect and say, select id
from album where title = 'Foo', and that
| | 03:22 | gives me all the tracks
there for the album called Foo.
| | 03:25 | So I can actually look at this data and
I can manipulate this data in SID, and
| | 03:29 | that gives me some insight into
what's going on over here in CRUD.
| | 03:33 | Now, if you notice, if I go back over
here to the main screen and I see my Foo,
| | 03:39 | Bar, Baz album there, notice
it's got the total duration.
| | 03:42 | It's got all the information there.
| | 03:44 | I can click Edit and I can see all the
tracks, or if I want to I can actually
| | 03:48 | delete the entire album.
| | 03:50 | If I press Confirm Delete, you'll notice now
that it's all gone, even the tracks are gone.
| | 03:55 | If I put in here #19, because I know
that that was the album id, the tracks are
| | 04:00 | gone, and if I select * from album, it
has actually deleted the album and it has
| | 04:08 | deleted all of the tracks.
| | 04:10 | So it's a fairly complete CRUD application.
| | 04:13 | Now, in the next movie we'll go ahead
and we'll look at the code that makes
| | 04:16 | this work.
| | Collapse this transcript |
| Using PHP to insert, read, update, and delete rows in a PostgreSQL database| 00:00 | Now we're going to take a look at the
code that makes the CRUD application work.
| | 00:03 | If you look in your CRUD folder in your
ExerciseFiles, you'll see that there's
| | 00:07 | an assets folder and inside that
assets folder is a little icon file and four
| | 00:13 | PHP files and an index.html file.
| | 00:16 | The index.html file is
really not even used at all.
| | 00:21 | It says, This page intentionally left
blank, and the purpose of it is in case
| | 00:26 | this is on a web server someplace and
somebody tries to look at a directory, I
| | 00:30 | do this in all of my unused directories.
| | 00:33 | You'll notice the same thing in the css
directory--there's another one of those.
| | 00:37 | The css directory has the main.css file,
and this is exactly the same as the one
| | 00:41 | for the SID program, which
explains the similar color scheme.
| | 00:45 | In the assets folder, you've got a footer.php.
| | 00:49 | There is a bit of HTML with some
variables that get replaced in PHP.
| | 00:53 | There is a header.php, similar deal there,
| | 00:57 | plain.php, which I think in this
case is actually not even used,
| | 01:01 | and main.php, which is the main
layout of the middle of the screen here.
| | 01:07 | And again, it's got variables that get
replaced and it allows you to do things
| | 01:11 | like, with these form fields there is
a little bit of PHP to echo the value
| | 01:16 | and things like that.
| | 01:17 | It's all very simple and
straight forward stuff here.
| | 01:19 | There is the bottom of that file.
| | 01:22 | But the main work is done here in crud.php.
| | 01:25 | So this is actually a fairly
long file, it's 1100 lines.
| | 01:29 | It's got a number of separate functions in it.
| | 01:33 | And the bottom line for this is that,
like so many applications it initially
| | 01:38 | started out as one thing and it kind of
grew. Initially it was a demonstration
| | 01:43 | for my PHP Essential Training course,
and then it got modified and made a
| | 01:48 | little bit more robust for MySQL, and
then it got extended to also support
| | 01:53 | SQLite and Postgres.
| | 01:55 | And so all of this is done in exactly
the same program, and in fact, if you want
| | 01:59 | to take this same program and run it
with SQLite, you just comment this section
| | 02:03 | out here, and you uncomment this
section here, and if you wanted to run with
| | 02:07 | MySQL, you just uncomment this section here.
| | 02:10 | You'll notice throughout the code, like
here in init, you'll see something like
| | 02:16 | this, a Switch statement on DBENGINE,
and if it's sqlite3, it uses this,
| | 02:21 | if it's mysql, it uses that,
| | 02:23 | and in our case, for Postgres,
which is pgsql, it uses this part here.
| | 02:29 | This is the beauty really of the PDO
interface in PHP is because even though
| | 02:35 | there is some different setup stuff
here, a lot of this code, if we come down
| | 02:39 | here to get_tracks_sql,
| | 02:42 | let's try one of the other ones here.
| | 02:46 | Here we are, insert_album_sql.
| | 02:49 | This is exactly the same.
| | 02:51 | This exact same SQL works
for any of those three engines.
| | 02:55 | And so this is true for a lot of it.
| | 02:57 | And then some of it, like for instance,
the one before it, the get_tracks_sql,
| | 03:02 | because the way that we handle dates
and because of some of the differences in
| | 03:05 | the SQL that's supported by the
different engines, we have this Switch statement
| | 03:09 | and we have different SQL for each of these.
| | 03:13 | For now, let's go back up to init, and a
lot of what I do in here has to do with
| | 03:19 | PHP and things that I like and
don't like about PHP, but we're going to
| | 03:24 | concentrate on the SQL parts here,
| | 03:26 | and so here's how I connect to the SQL database.
| | 03:29 | So I'm using PDO, so I have
a PDO connect string here.
| | 03:33 | I set the client_encoding to latin1, and
I get the version of the Postgres server.
| | 03:39 | And so that ends up being displayed
right down here where it says Postgres
| | 03:43 | server version 9.1.1. But we're going
to skip through some of this, and like I
| | 03:51 | said, we're just going to concentrate on the
parts that actually interact with the database.
| | 03:55 | One of the first things we do is we get_
albums, and get_albums is just a simple
| | 03:59 | Select on the album table.
| | 04:02 | In here, also at some point we called
get_album_length, and that's to display
| | 04:07 | this length of the album down here.
| | 04:10 | You'll notice that I have two different
versions of the SQL for this, from the
| | 04:14 | SQLite and the Postgres version.
| | 04:16 | This wasn't a feature I had added
yet when I did the MySQL version.
| | 04:19 | I haven't gone back and added it yet.
| | 04:21 | But you'll notice for the Postgres
version I sum up all of the durations of the
| | 04:26 | tracks where the album_id is the right album,
| | 04:30 | and then I take the integer division for
the minutes and the integer modulus for
| | 04:35 | the seconds, and for the modulus I use
this Format string to make sure it has
| | 04:40 | that leading 0, and then I just
concatenate it with the colon.
| | 04:44 | So again, that gives us this display
format here, 53 minutes 27 seconds for that
| | 04:49 | album at the bottom.
| | 04:50 | Let me back up to get_tracks.
| | 04:56 | get_tracks is another one where we
have to do the same kind of a trick for
| | 05:01 | the duration, and so that makes this
different for each of the different database engines.
| | 05:05 | And this comes into play
when we go over to the tracks,
| | 05:08 | let's click on the tracks for this one
here, and you see we're displaying each
| | 05:12 | of the tracks with that duration.
| | 05:16 | Then we have the insert_album_sql, again,
this is pretty straightforward, the SQL is
| | 05:20 | the same for each of the engines.
| | 05:22 | The one difference here is that PDO has
this lastInsertId model which comes from
| | 05:29 | MySQL, which is probably the
most popular engine on the Internet.
| | 05:33 | And Postgres doesn't exactly support
that model, but what it does have is a way
| | 05:39 | of getting the current
position of the sequence engine.
| | 05:43 | Remember that the serial primary key
model is supported by a sequence engine.
| | 05:49 | And if you want to get the current
sequence of a particular sequence you can
| | 05:52 | pass lastInsertId the name of that
sequence and it will return exactly the same
| | 05:57 | value, and it makes it work like
MySQL and also like SQLite, which imitates
| | 06:04 | MySQL in this particular behavior.
| | 06:06 | We do the same thing when
we're inserting a track.
| | 06:08 | Again, the SQL is very straightforward,
and we have this same technique for
| | 06:14 | getting the lastInsertId.
| | 06:18 | update_album is exactly the same
for all of them and it's a simple
| | 06:22 | update statement in SQL.
| | 06:24 | And you notice we have a prepare and an
execute where we provide the parameters.
| | 06:29 | update_track_sql, again, very straightforward.
| | 06:34 | delete_album_sql uses two different
queries and has to do this in two steps, so
| | 06:39 | that it make sure that it deletes all
the tracks before it deletes the album.
| | 06:43 | And that's a really important sequence,
because if the operation were to be
| | 06:47 | interrupted, and you had deleted the
album, but you hadn't deleted the tracks,
| | 06:50 | those tracks would be orphaned forever.
| | 06:52 | There wouldn't be any easy way for
you get in there and clean that up,
| | 06:56 | except perhaps manually.
| | 06:58 | So first we delete the tracks, which
happens here, check for errors, and then
| | 07:03 | we delete the album.
| | 07:07 | Here is where we delete an
individual track and again, that's very
| | 07:10 | straight forward, and
fetching an individual album.
| | 07:15 | And finally, here we have the fetch
track, which we've looked at before, which
| | 07:19 | has a little bit of extra
logic for handling the duration.
| | 07:22 | So that's a quick overview of
the SQL parts of this application.
| | 07:27 | Of course you've got the whole
source code here and it's even part of the
| | 07:31 | free Exercise Files, so if you don't have a
Premium subscription, you still have these files.
| | 07:36 | So feel free to go through it, look
at it, modify it, and use it for your
| | 07:40 | own purposes.
| | Collapse this transcript |
| Installing the testimonials database| 00:00 | Before you can test or use the
testimonials database applications, you'll need to
| | 00:04 | install the testimonials database
and this is really very easy to do.
| | 00:10 | Here you see I have the pgAdmin III
application open on my desktop and in the
| | 00:15 | Object browser over here on the left I
have Databases selected, and over here on
| | 00:20 | the right it lists the four
databases that we've had all along.
| | 00:24 | And here we are going to create
a new one called Testimonials.
| | 00:28 | So with Databases selected in the
Object browser, I just press this cool Create
| | 00:32 | One of These button and it
brings up the New Database dialog.
| | 00:36 | So I type the Name of the new database
and the owner will be Web, just like the
| | 00:43 | other ones, and of course that means
that it can be used by the SID user, which
| | 00:47 | inherits the web user, and I just
press OK and we have a new database, unless
| | 00:53 | it's not showing up here.
| | 00:54 | If I press Refresh, there it is.
| | 00:56 | Sometimes that happens.
| | 00:58 | So now I select the testimonials
database in the Database browser on the right,
| | 01:04 | and I just press the SQL button up
here to get the SQL query browser.
| | 01:09 | And I press the open button and with
Exercise Files selected over here in my
| | 01:15 | Exercise Files under Testimonials and then SQL.
| | 01:19 | There is the SQL for creating the
tables and populating the data in the
| | 01:24 | testimonials database.
| | 01:26 | It's called testimonials-pg.sql, and I click
Open and Execute query, and there we're done.
| | 01:37 | Now we have successfully created and
populated the testimonials database.
| | 01:42 | Now before we contest it, we need to
make sure the passwords are set correctly
| | 01:46 | in the scripts that will be using the
testimonials database, so I'll close this
| | 01:50 | and I'll come up here and I'll open db.
php, and right here on line 15 you'll see
| | 01:56 | I have the string foo.bar.
| | 01:58 | This is where you'll need to put the
password for your SID user, exactly as you
| | 02:01 | entered it in pgAdmin.
| | 02:04 | So we'll go ahead and close this and open
the testimonials.php and we do the same thing,
| | 02:10 | this time the password is on line 13,
and again, you see the string foo.bar.
| | 02:16 | This is where you'll need to put the
password for your SID user exactly as you
| | 02:19 | entered it in pgAdmin.
| | 02:22 | Now let's open it in the
web browser and test it.
| | 02:25 | So we'll come down here and open up
Firefox, and I am going to click on my
| | 02:29 | Exercise Files and on testimonials, and
I am going to open test page.php. That's
| | 02:36 | the page that displays the testimonials.
| | 02:39 | And there you have it.
| | 02:40 | This is just a little test page and
you see the testimonials on the right.
| | 02:45 | There are just little quotes I have
in the database, they are not actually
| | 02:48 | testimonials and if I press Reload,
I get a different random set of three
| | 02:53 | testimonials each time.
| | 02:54 | We'll be looking more closely at how
this works in the rest of the chapter, but
| | 02:58 | for now the testimonials database
is installed and it's ready to use.
| | Collapse this transcript |
| An overview of the testimonials application| 00:00 | The other application we are going
to look at in this chapter is the
| | 00:03 | Testimonials application and
here I've brought this test page.
| | 00:07 | This is in Exercise Files in the
Testimonials folder and it's called
| | 00:12 | testpage.php and you notice that
every time you load the page, it shows this
| | 00:17 | box of testimonials, and every time
you reload, the testimonials change.
| | 00:21 | And so you see that they are
different sizes, so the page wraps a little bit
| | 00:25 | differently, in this case they are
just little quotes, but the reason I
| | 00:29 | originally designed this application
was, because on my web site I wanted to
| | 00:32 | have a little box with some
testimonials from customers, clients and people
| | 00:37 | who've viewed my courses.
| | 00:38 | And so it's a simple little application.
| | 00:41 | If we look here in the testimonials
folder, you'll see there is an assets
| | 00:45 | folder that has some of the HTML pages as PHP
because the application is written in PHP.
| | 00:51 | And it's got a couple of CSS files;
| | 00:54 | all of this is included in your Exercise Files.
| | 00:56 | There is a db.php, and this is the
main interface for editing the database.
| | 01:01 | We'll look at that in a moment.
| | 01:02 | There is a testimonials.php, and
this is the interface for reading the
| | 01:06 | testimonials database and displaying
these random testimonials on the page. And
| | 01:11 | there is the test page itself, which is
the page we're seeing displayed there.
| | 01:15 | If I bring that up, you see it's mostly
HTML and it has this little bit of PHP
| | 01:20 | in it right here to include the
testimonials application itself, and the rest
| | 01:25 | of that is just HTML.
| | 01:26 | Now when you want to manage the database,
you use this db.php and you notice it
| | 01:33 | says at the top here there are 14
testimonials in the database, add some more.
| | 01:37 | And see the interface is very similar
to what we saw in the CRUD application.
| | 01:42 | It's a layout that I happen to like,
and so you can enter a new one at the top,
| | 01:46 | or you can edit the existing ones at the bottom.
| | 01:48 | And here is the difference here is we
have this little paging panel here, where
| | 01:52 | if I click the right arrow
it will take me to page 2,
| | 01:54 | If I click it again, it takes me
to page 3 and now it's grayed out.
| | 01:58 | I can click directly on any of the
pages, and as we add more testimonials, it
| | 02:05 | simply adds pages and that makes it
easier to manage having a lot of data.
| | 02:10 | And I always hoped to have a lot of data,
but I haven't really managed mine too
| | 02:13 | well on my web site.
| | 02:14 | I don't take a lot of
time to manage my web site.
| | 02:16 | These are listed alphabetically, and if
I want to click on one of these, click
| | 02:21 | on Muhammad Ali, Don't count
the days, make the days count.
| | 02:24 | If I wanted to I could edit it, and
click Update, and now about one is updated
| | 02:32 | in the database there.
| | 02:33 | I think that was on page 2 I guess.
| | 02:36 | I can edit it again and take that
extra text out, update the testimonial.
| | 02:42 | Or I can add a new one. Add
that, and we see there it is there.
| | 02:56 | I can Edit it, or I can Delete it.
| | 03:01 | And so it's all pretty straight forward.
| | 03:03 | In the next movie we'll start
looking at how the code works.
| | Collapse this transcript |
| Managing the database in PHP| 00:00 | In this movie we're going to look at
the code for the testimonials database
| | 00:03 | management function.
| | 00:04 | In your Exercise Files you find this
under the Testimonials folder and in db.php.
| | 00:12 | This is actually a pretty
straightforward piece of code.
| | 00:14 | At the top of the code here you'll
find some constants are defined and we are
| | 00:18 | also, including the bwPgSQL.php library.
| | 00:23 | So this definitely does use that
library and you can see how using a library
| | 00:28 | like this makes the code quite a
bit cleaner and easier to code.
| | 00:32 | So you'll notice here in the init
function I instantiate a new bwPgSQL object
| | 00:40 | and there is a little jump function
here that makes managing the state of the
| | 00:43 | web connection quite a bit easier based
on an action variable, whether to add,
| | 00:48 | edit, update, delete.
| | 00:50 | So that makes that all pretty easy.
| | 00:53 | So adding an item is simply a matter of
running some PHP escapes on it to make
| | 01:00 | sure that it's safe and then calling db insert.
| | 01:03 | And if there is an error I catch it with the
PDOException, and display the error message.
| | 01:09 | In delete_confirm I go out and get
the record with the get_rec method and
| | 01:15 | display it and give the option to
confirm deleting this particular testimonial.
| | 01:21 | And when it's confirmed, I go ahead
and do it using the delete method.
| | 01:27 | In update, again, we escape with the
PHP htmlentities, and call the update
| | 01:34 | method in the CRUD library.
| | 01:36 | Seeing a pattern here? In the edit
function we use get_rec to get the data to
| | 01:40 | display and offer the user the
opportunity to edit that record.
| | 01:46 | For listrecs it's just a little bit
more complicated, because we have the
| | 01:50 | paging thing going on.
| | 01:52 | Remember that we're
displaying these items by page.
| | 01:56 | So we need to do a little bit of math
and decide how many we're displaying on a
| | 01:59 | page, remember up here at the top there
is a page limit and that's the constant
| | 02:04 | that says, how many we are
displaying on a page at a time.
| | 02:08 | And so we do a little bit of math
around that right here, and then we set up
| | 02:14 | the paging bar and we call this list_
pagebar function, which we'll get to in a
| | 02:17 | moment and then, because this is a
little bit more complicated than a standard
| | 02:22 | CRUD operation, we have just a
little bit of SQL in here using the LIMIT and
| | 02:26 | OFFSET clause for SELECT, and that
allows us to display, for example, 5 on a
| | 02:31 | page 5 in, or 10 in.
| | 02:36 | And here we have the sql query function
from the bwPgSQL library and you notice
| | 02:41 | it's being used as an
iterator inside the foreach loop.
| | 02:45 | Listing the pagebar, this doesn't have any
SQL, it doesn't actually access the database,
| | 02:50 | but is database related and it's simply
a matter of knowing what page number run
| | 02:54 | and how many pages there are and
then displaying the HTML for that.
| | 02:59 | That's pretty much it, what's left
here is managing the page, managing the
| | 03:03 | forms, same variables, callbacks,
html forms, hidden variables, messages
| | 03:11 | and error messages.
| | 03:12 | So you can see this is pretty straight forward,
| | 03:15 | it's only about 350 lines.
| | 03:18 | Comparing this to the CRUD application,
you can see just how much simpler it
| | 03:22 | is to deal with the database parts of
an application when you have a decent
| | 03:27 | CRUD library.
| | Collapse this transcript |
| Displaying the testimonials using PHP| 00:00 | In the previous movie we looked at
the code for managing the database for
| | 00:03 | testimonials application.
| | 00:05 | In this movie we are going to look at
the code for displaying the testimonials.
| | 00:09 | Here you see them on the page here and
when I reload, you see we get a different
| | 00:13 | set of random quotes.
| | 00:16 | Of course, these will be actual
testimonials, in the real application,
| | 00:18 | we just got to fill it
with some random quotes here.
| | 00:21 | And so in this movie we are going to
look at the code that actually does that.
| | 00:24 | We'll take a given number of quotes out
of the database and display them at random
| | 00:29 | every time it's reloading.
| | 00:31 | So looking in our testimonial's
folder from the Exercise Files you will see
| | 00:35 | a PHP file here called testimonials.php
and we are going to open that in our editor.
| | 00:41 | This one is really very simple.
| | 00:43 | It's only 92 lines long.
| | 00:45 | At the top here we have our
constants for connecting the database.
| | 00:49 | You will notice that there is an init
function where we actually do connect to
| | 00:54 | the database and that should all
be very familiar at this point.
| | 00:57 | We are using the bwPgSQL library for this.
| | 01:01 | PHP makes some of this easy for us.
| | 01:04 | I have done this same application in a
number of different languages, because I
| | 01:06 | use it as an example in my
language courses as well.
| | 01:09 | So the first thing we do here is we go out
to the database and here in the foreach loop,
| | 01:15 | we use this query to just grab an array
of the IDs and then the number of IDs is
| | 01:24 | done by counting the members of the array.
| | 01:26 | That's a PHP function.
| | 01:28 | Here we check to see if the amount of
random quotations we want is more than we
| | 01:33 | can reasonably expect to do at random
out of the database, and if so, we just
| | 01:37 | display a message, and I just
arbitrarily decide you need to have four times as
| | 01:41 | many in the database as the number
of random items you want to display.
| | 01:45 | And then this lovely PHP function here,
array_rand will give you a certain
| | 01:50 | number of random items out of an array,
so I didn't have to right there in
| | 01:54 | PHP, that was already done for me.
| | 01:56 | And then I grab those IDs out of list
and I go ahead and I call printrec with
| | 02:02 | each one of them and printrec simply
goes out of the database and grabs the rec
| | 02:06 | with get_rec and displays it with printf.
| | 02:10 | It's as simple as that.
| | 02:11 | So that's a very simple application for
displaying random quotes out of the database.
| | 02:15 | Of course, that's the easy part, the
managing the database part is what we
| | 02:19 | covered in an earlier movie in this chapter.
| | Collapse this transcript |
|
|
ConclusionGoodbye| 00:00 | In this course my goal was to give you a
good understanding of Postgres 9 so you
| | 00:04 | can use it with PHP to build powerful
and compelling applications for yourself
| | 00:09 | and for your clients.
| | 00:10 | I have covered the basic features of
Postgres, its functions, its use of SQL,
| | 00:14 | and I have walked you through some
real-world PHP applications that use
| | 00:18 | Postgres so you can apply your knowledge to
building your own projects with Postgres and PHP.
| | 00:24 | I have really enjoyed creating this
course for you and I sincerely hope that
| | 00:27 | it's as useful for you
as it has been fun for me.
| | Collapse this transcript |
|
|
