Drupal: Looking at Drupal’s database | lynda.com

Drupal: Looking at Drupal’s database | lynda.com


Now we are ready to talk about Drupal itself. Keep in mind that Drupal
essentially is just an attractive and flexible interface to a back end
database. So it’s worthwhile to take a look at that
database to understand what exactly happens when you create, modify, and
delete content in Drupal. This
understanding will make Drupal administration a lot clearer overall and it
opens up a world of possibilities if you are an advanced programmer or a
database administrator. We are working with a pretty clean installation
of Drupal here. We are going to
mess it up a little, so we’ll be ready to roll it back to its previous state we
are done. The Using the Exercise Files chapter of this
course shows you how to save and restore a database, so be sure to
watch that video if you decide to follow along and you end up making changes
that you decide not to keep later. First, we have our site right here. Let’s go and take a look at the database
that’s sitting behind it. To do so, we go up to phpMyAdmin and click
on lynda, which is the name of the database we are using
for this course. Each of the
things you see here in this left column and here in the main part of the screen
are individual tables. The names of these tables are fairly self-explanatory
for the most part. For example, let’s take one of the simpler
ones. User, we go
down and we click where it says users here and we see the structure of the database. Each one of the rows here: name, pass, mail,
and so forth is a field in that table. The mail field for example stores the email
address for that user that the user gave when they created that account. If you click on the little icon to the left
of that word user here, you get to browse the database. The other way to do that is by clicking on
Browse up here, then you see the values that are in the database
itself. Right now we have two users. When you create a Drupal site, user ID 0 is
the anonymous user. It’s sort of the placeholder for people who
haven’t actually logged into this site. User ID 1 is the super administrator who can
do everything. From then on, everybody else is an authenticated
user. Information about these roles is stored in
another table, here called role. In
the roles table, we see the two roles that are setup in Drupal automatically. Role ID 1 is the anonymous user. Role ID 2 is an authenticated user. If we were to setup other roles later, they
would be roles ID 3, 4, and so forth, and they would have whatever names
we gave them. Then when we create
users and assigned them to that role, those two would be connected so that a
user would have a role ID. Let’s go back briefly to our users table however. And then let’s go back to our
site and actually create a user. To do so you would go to Administer>User
management>Users>Add user. So under Username, we are going to make it
tom, E-mail address [email protected] Password, we’ll just say password. Password of
course we have to confirm, password. Scroll to the bottom. Active user, we are
not going to notify them of it, but we create the account. Good, and it’s all done. Now that we’ve created that user, let’s see
what actually happened in the database. So we go back to phpMyAdmin, and click again
to browse the user’s table. You’ll notice there is tom, user ID 3. I think somewhere in the middle
there I created another user and then deleted it, so instead of saying 2 it was
3. It will keep increasing the user ID automatically,
so you don’t have to worry about it. But you can see there is our Password, it’s
encrypted so that someone who is looking at the database can’t simply see it. The e-mail address, some flags on
it to say whether or not they are an active user, when they last logged in and so forth. This example is going to be more complex,
because when you create a node in Drupal, it actually affects many more tables. If you look along this left-hand
side, you’ll see node, node_access, node _common_statistics, etcetera, but let’s
go ahead and do that we’ll see how it works. So we go back to our site and Create content. This time I’m going to create a
story and I’m going to say “Hello again, world!” The body is “Did you miss me?”
and I’ll scroll to the bottom and since this is a story, it will go to the
front page automatically that’s in Publishing options here, and Save. If we go
back to our front page, we see there it is. Now let’s go back to our database and see
exactly what happened. We click up
here to go back to phpMyAdmin. Let’s start with a node table. And there we see
the first page we created earlier in this course, Hello, world! and there is
our second one. It has a node ID, known as nId, a version
ID, vId, and a lot of other information in here, but interestingly,
it doesn’t include the body of the node. That is actually referred to in another table,
in node_revisions. If
we go there, so you can see the Drupal actually keeps a complex set of
relationships between node tables, and node revisions tables, and even the user
tables. Here you can see which user created it, with
the uId. For those of you who want to get really deep
into Drupal’s database, I would like to suggest a few resources. The first one is a module called schema, and
it’s available at drupal.org/project/schema. A schema describes how a database is built,
and this module will show you how your particular database is created and interacts
a little bit better while you are running your Drupal site. The second module is called devel. Devel is for any kind of Drupal developer. It will show you the SQL commands that are
being sent to and from the server, PHP messages, very useful if you are doing
any sort of high-end Drupal development. So, that gives you an introduction to how
Drupal handles data. I know this
knowledge may seem arcane now, but it will help you in many ways beyond this
course. For example, if your Drupal site is broken,
you might be able to fix it by manipulating the database directly. And a thorough understanding of the
database is essential to importing legacy data or to writing custom modules
that use data in some way. The functions of those 45 or so tables installed
in Drupal by default, and the tables installed by dozens and dozens of common
modules are a worthwhile study if you want to go further with your Drupal
database knowledge. All it takes is
phpMyAdmin, a Drupal installation and plenty of time.

Danny Hutson

Leave a Reply

Your email address will not be published. Required fields are marked *