Using Schema Module

Video loading...

  • 0:01
    Using Schema Module
  • 0:05
    with Addison Berry
  • 0:13
    This video is about the Schema Module, which
  • 0:15
    is a really handy module that basically
  • 0:17
    lets you look at your Drupal database through the Drupal site.
  • 0:22
    So two main aspects of it are that you
  • 0:26
    can get information about the database tables and fields.
  • 0:30
    And then it can help you generate a structure for a new module
  • 0:35
    that you're building based on the data
  • 0:37
    that you've already created in the database.
  • 0:39
    Now, there's a Drupal 5 and Drupal 6 version for the module.
  • 0:43
    But the new schema API went in and started in Drupal 6.
  • 0:47
    So that's what we'll be looking at.
  • 0:49
    I've got a basic Drupal 6 site set up already.
  • 0:52
    And the first thing we need to do is actually enable the module,
  • 0:56
    so download it and install like any other module that you would have.
  • 1:04
    And once that is done you'll notice under Administer, Site Building,
  • 1:09
    we now have a new section called schema.
  • 1:12
    And when you go here, you'll see all the different kinds of tasks
  • 1:15
    that you can do.
  • 1:17
    The first thing is comparing.
  • 1:18
    It's simply going to compare your database.
  • 1:20
    It'll look for mismatches.
  • 1:21
    So if something's changed in your database,
  • 1:24
    missing tables, and extra tables.
  • 1:26
    So this is just a quick check to say does your database actually
  • 1:29
    match what the code expects.
  • 1:32
    And we're going to look at what a mismatch will do here.
  • 1:35
    I'm going into my database, and going into the access table.
  • 1:41
    And I'm going to edit this status field of my access table.
  • 1:46
    I'm going to change the type from Tiny INT to INT.
  • 1:50
    So it's not like a massive change.
  • 1:52
    But it's definitely a change.
  • 1:54
    And when I go back into my Drupal site that's
  • 1:57
    running on this database, now it's telling me
  • 2:00
    I actually have a mismatch.
  • 2:02
    And so it tells you which module created
  • 2:07
    the table that's been affected.
  • 2:09
    It tells me that this is the access table that's been changed.
  • 2:12
    And it also tells me which column within that table, which
  • 2:16
    is the status column, status field.
  • 2:19
    And then it just basically gives me, in Drupal code,
  • 2:24
    what the changes are.
  • 2:25
    So this is what is declared by the user module.
  • 2:28
    So when users install file this is what it's looking for.
  • 2:32
    And in the database this is what we have.
  • 2:36
    So we have this width.
  • 2:38
    And the size is missing.
  • 2:39
    It's not tiny, as far as Drupal schema's concern.
  • 2:44
    So I'm going to go back and flip this
  • 2:48
    back so that I don't completely mess up my Drupal installation.
  • 2:53
    So I'll change it back from an INT to a Tiny INT.
  • 2:55
    I save that.
  • 2:56
    Go back to the site.
  • 2:58
    And now when I reload it'll tell me that it's not mismatched anymore.
  • 3:02
    So now my database matches my code, as it expects.
  • 3:07
    The next handy thing here is this Describe.
  • 3:10
    This is pretty darn cool, and something
  • 3:12
    that you can only get Drupal 6 because of the new schema API.
  • 3:16
    Basically what this does is it tells you what
  • 3:19
    all the stuff in the database is.
  • 3:22
    So it has the table.
  • 3:24
    And it has the module that's created that table listed next to it.
  • 3:29
    So if we open something up here, you'll
  • 3:31
    see it lists all of the fields.
  • 3:34
    It has descriptions for them.
  • 3:37
    Let's go down and look at boxes.
  • 3:39
    Boxes is always a weird table.
  • 3:40
    Everybody's like what it the world is boxes?
  • 3:43
    So it tells me the block module's creating this.
  • 3:47
    It has my field name.
  • 3:49
    It gives me the type.
  • 3:51
    You can see the different fields it's looking at.
  • 3:53
    The cool thing is this description bit here.
  • 3:55
    It's actually telling me what that field's for.
  • 3:57
    It's for the block's
  • 4:00
    Now that looks like a typo.
  • 4:02
    But the block's here is referring to another table.
  • 4:07
    So it's referring to the blocks table.
  • 4:10
    And the bid is the field within that table.
  • 4:13
    So if I click on that blocks link it takes me to the blocks table now.
  • 4:17
    And I can see that it has a bid.
  • 4:20
    So those two are related to each other.
  • 4:23
    And we have the other fields and information,
  • 4:27
    same thing down here a format.
  • 4:28
    Again filter formats.format.
  • 4:32
    Filter format is another table in our database,
  • 4:36
    which is shown down here.
  • 4:37
    And it's basically linked up to the format field in that table.
  • 4:44
    So if we go into the database itself and looked at boxes,
  • 4:47
    you'll see there are the four fields.
  • 4:49
    But in the database I don't know what they're for.
  • 4:52
    But using schema I have some description about what's going on
  • 4:55
    and how they're related to other things, which is really handy.
  • 4:59
    It also has a description of the overall table.
  • 5:02
    So it's for custom made blocks, called boxes.
  • 5:05
    It stores contents of custom made blocks.
  • 5:07
    There you go.
  • 5:08
    Same thing, off map, people are like what is that?
  • 5:11
    Well that's a distributor authentication mapping.
  • 5:14
    So the describe functionality that you get with schema is awesome.
  • 5:20
    Now that would only exist if people actually
  • 5:24
    put descriptions in, which we'll talk about a little bit later.
  • 5:29
    Under inspect you actually get whatever
  • 5:31
    you get the Drupal schema array that would
  • 5:35
    be necessary for the various table.
  • 5:37
    So the block module has created all of these.
  • 5:39
    And you'll find this in the install file.
  • 5:44
    Although, this is pulling it from the database.
  • 5:46
    It's not pulling it from the install file.
  • 5:49
    The SQL tab just gives you the straight pure SQL for the database.
  • 5:55
    And then show gives you an array printout of what's going on.
  • 5:59
    And you'll see that here's where this description is.
  • 6:02
    It's part of this array in the schema.
  • 6:04
    And we'll look at that in a little bit more detail in a second.
  • 6:08
    So let me pull over my text editor here with the file.
  • 6:14
    So I am looking at the block module's install file.
  • 6:21
    So I'm in the block module, core block module, block install.
  • 6:26
    And if I scroll down here a bit you'll
  • 6:31
    see that basically this is the array that
  • 6:33
    is setting up all of the tables and fields in the database.
  • 6:39
    And so here you can see this is for the boxes table.
  • 6:43
    We have the description for the boxes table.
  • 6:45
    So this what we saw on that describe tab.
  • 6:48
    And then underneath that table we have the fields listed.
  • 6:52
    So there's the bid.
  • 6:54
    And there's a description for that.
  • 6:55
    And this is where we had that link to the blocks table
  • 6:59
    and such in our descriptions.
  • 7:01
    So if a module does not have this description line in the array
  • 7:07
    in it's dot install file then you won't get anything useful
  • 7:11
    on that describe tab.
  • 7:13
    And if you do you find a module that doesn't have that then go ahead
  • 7:16
    and create an issue in the issue queue for that.
  • 7:19
    And create a patch.
  • 7:20
    And say hey a description would be really handy for this.
  • 7:23
    Here's a patch.
  • 7:25
    And then everybody can benefit from them.
  • 7:30
    So that's just looking at stuff and descriptions.
  • 7:33
    But the next thing I want to do is actually
  • 7:35
    create a new table as though I was creating a new module that needed
  • 7:41
    a new table, and see how this can help me generate the code
  • 7:46
    that I need to actually put in my install file.
  • 7:49
    So I'm creating a new table for my module.
  • 7:53
    And I'm just going to go ahead and put some fields.
  • 7:55
    I'm just going to put two fields in here
  • 7:58
    and just sort of pick some stuff sort of randomly just
  • 8:02
    to give you a sense of how this works.
  • 8:06
    And I'm just in the database, using php my admin,
  • 8:09
    making stuff as I figure out how to make my module work.
  • 8:13
    I'm going to go ahead and make that ID a primary key.
  • 8:18
    And then I'll go ahead and just save this table.
  • 8:20
    So now I have a new table in my Drupal database
  • 8:24
    that I want my custom module to use for some reason for stuff I need.
  • 8:31
    So if I go back after I've added that and I go to the inspect tab
  • 8:35
    now you'll see that down there are the regular modules.
  • 8:38
    But this has an unknown.
  • 8:39
    Because it doesn't know what's going on yet.
  • 8:41
    There's just this extra table suddenly in the database.
  • 8:44
    But it's seeing that.
  • 8:45
    If you go back to compare you can see it's showing that we have
  • 8:47
    extra, we have an extra table now.
  • 8:51
    And so it's also set everything up in this nice Drupal schema
  • 8:57
    API is going to be looking for in order
  • 8:59
    to build the database tables that it needs.
  • 9:03
    So what I'm going to do is go back over to my text editor here.
  • 9:06
    And I'm actually going to start putting
  • 9:08
    this together for my module.
  • 9:10
    So I don't need to be in the block module anymore.
  • 9:15
    Let me go there.
  • 9:17
    I do want to use it just as a quick copy paste.
  • 9:20
    I'm going to go into my modules file.
  • 9:23
    And this is my module dot install file.
  • 9:27
    I don't have anything in there yet.
  • 9:29
    And what I'll do is go up here and it's always
  • 9:32
    handy to just copy stuff from existing
  • 9:35
    rather than typing everything out.
  • 9:37
    So I need a block schema hook.
  • 9:41
    And so that's the basics.
  • 9:45
    And then it needs to return schema array.
  • 9:51
    So put that in there.
  • 9:53
    And now I need the actual schema array that actually tells Drupal,
  • 9:59
    this is what I need you to build in the database for me.
  • 10:02
    So what I can do is go back over here.
  • 10:05
    Now we have this handy dandy inspection, which is showing me
  • 10:09
    what the array is to build what I have in my live database.
  • 10:14
    All I have to do is copy and paste that in.
  • 10:16
    And I'm going to do a little bit of formatting.
  • 10:18
    Because this is just making me a little nuts just looking at it.
  • 10:23
    So let me just format this real quickly here,
  • 10:28
    make it a little bit easier to read, and just generally
  • 10:31
    kind of a good idea to format everything
  • 10:35
    so that it's easier to read in your module, for your sanity
  • 10:38
    and for other people who are looking at your work.
  • 10:42
    So each of these arrays should be indented.
  • 10:48
    Sorry, I know this is a little bit tedious.
  • 10:52
    But, seriously it makes it much nicer to work with.
  • 11:01
    Make sure you have commas after your array items.
  • 11:06
    That's a Drupal coding standard.
  • 11:09
    All right, that looks more better.
  • 11:12
    OK, so this is the basic thing.
  • 11:15
    This is basically what I need in order to build
  • 11:18
    the stuff that I've created.
  • 11:21
    There's the table name.
  • 11:24
    And what it is missing is the description.
  • 11:27
    Because that's not in the database.
  • 11:29
    That's something that's only in our install files.
  • 11:31
    So what I need to do is add a description in here for the table.
  • 11:37
    So this top one here is for the table.
  • 11:40
    And I'm just going to put stuff in here.
  • 11:42
    Obviously the more detailed description
  • 11:45
    you can put the more useful it will be to both yourself,
  • 11:49
    co-workers, and other people who are looking at your module.
  • 11:53
    So and then I'm going to just copy paste that into the two fields
  • 11:58
    I have here.
  • 11:59
    So I have my ID.
  • 12:00
    So I get a description under the field name as well under my ID.
  • 12:04
    So I'll change this.
  • 12:06
    Now remember this was a primary key.
  • 12:07
    So that's probably an important bit of information.
  • 12:09
    So I'll make sure I note that in my description.
  • 12:12
    And it's the main ID for my module.
  • 12:18
    Same thing down here for the test field.
  • 12:20
    So I need a description for that field as well.
  • 12:24
    I'm not particularly good at just sort of making up random stuff.
  • 12:29
    But obviously you would put information that was actually
  • 12:32
    useful in here to explain to people what this field is actually going
  • 12:37
    to be used for in your module.
  • 12:39
    And so now I can save that.
  • 12:41
    Now I still need to do my hook install, hook uninstall,
  • 12:43
    and the other stuff like that.
  • 12:44
    But this is how it would build the schema for my module.
  • 12:49
    So it did all that work for me.
  • 12:52
    All I had to do was actually just create it.
  • 12:54
    And then schema module went ahead and read that, and figured out what
  • 12:57
    I needed to do in order to get it into my modules code
  • 13:02
    without a whole lot of me trying to figure out,
  • 13:04
    what was that, an INT, or a tiny INT, what is that.
  • 13:07
    Just let schema module do the work for you.
  • 13:10
    So that's a brief introduction to schema module, a very cool module.
  • 13:13
    You should definitely check it out.
  • 13:15
    And have fun.

Using Schema Module


The Schema module is a great tool when working with databases. You can use it to inspect your site's database, to see if there are changes, and to get more detailed descriptions for what all of the tables and fields are being used for. You can also use Schema to create the code you need to create database tables and fields. In this lesson we take a look at Schema's inspection features, and then we add a new table to the database for our custom module, build it out with some fields, and have schema generate the code, which we can just copy/paste into our custom module's install file.

* Note that this video was originally recorded in 2008, using the Drupal 6 version of the module, but the same features are available in the Drupal 7 version as well.

Additional resources: