Drupal 7 Module Development Part 5 - Database tables

By shane
Tue, 2012-10-02 13:10
Daily Dose of Drupal Episode #20

Share with Others

In this example we create a new Drupal 7 module with the purpose of creating a database table inside our Drupal 7 module. We look at hook_schema and the Drupal Schema API to create a new database table.

In this episode you will learn:

  • How to create a Drupal 7 module with an .install file
  • What hook_install, hook_uninstall, and other various installation hooks do inside your .install file
  • How to implement hook_schema in your Drupal 7 module
  • How to use the Schema API for create Drupal database tables

Welcome to another Daily Dose of Drupal and this is Episode Number 20, the big 20 and today we’re going to continue with I guess the trend that I’ve been starting and continue learning more about Drupal Module Development.

Today we’re going to look at the Install file in a module and how you can use that to create a Database Table and after that we’re going to in the next episode or two we’ll look at how to create a form and then populate that Database Table and run a few Database queries on our created table. One thing to keep in mind is in most cases you really don’t need to be creating Database Tables that often unless you’re really building something custom that actually needs it.

Make sure you really look at it and see if you can build it with the existing tools using Drupal Content Types in the Field API and Fields in general and you may not even need to create a Database Table to store the data. I guess it’s better to reuse what’s already available rather than rerunning it yourself and so just make sure you actually take inventory of that and see if it actually fits your scenario better than building something from scratch.

In this example it’s something you could easily do with Content Type and some Fields or all actuality even probably a Webform but we’re going to use it as a demonstration to show how the Database Schema can be created. And so we’re going to start by creating a new custom Drupal module; so I’m in my Drupal module’s folder here, I’m going to create a new folder, I’m going to call this formexample because we’re going to eventually be creating a form here so I’m going to create a formexample.info file, a formexample.module file and if you’ve been following along this would be a new one in the Daily Dose of Drupal, formexample.install.

So we’re going to open this up here; so in our Info File I’m just going to go ahead and give it a name form example, give it a description, the core is going to be 7.x, the version is going to be 7.x-1.0 and that’s all we really need for the Info File so I’m going to save that. We’re going to leave our module file blank, I could put in the file block or some comments here but we’ll just leave it blank and the Install file is the next step and there’s a couple of thing you’re going to need to look at when you’re creating an Install File.

The First is hook_install and this can be useful if you need to do something when you’re module is installed, maybe insert some entries into a Database Table maybe create a couple of Variables, things like that. There’s also a hook_uninstall and you can also see there it has some See Also on here, so hook_schema we’re going to talk about that in a second, hook_enable, hook_disable, hook_uninstall and there’s things that you’re going to take a look at if you need to perform actions when a module is turned on, turned off on a website.

hook_schema is actually where your Database Table is going to be defined and when you install your module this Database Table will then be created. We’re going to create a very simple Database Table to begin with but one thing you want to note is hook_schema has a lot of documentation here; it talks about how to create one and you can go from there but there’s also a Schema API page on api.drupal.org and this goes into much more detail on all available options in hook_schema and how you can use that to create Database Table as well as some Database functions and methods.

So take a look at that if you need to look into creating Database Table, we’re going to go ahead and start as always by just grabbing this code and dropping it into our Install File and we’re going to call this Database Table and this is where we define the name, I usually like to define my Variables first but it’s just a preference thing and you can give your Database Table a description and now we can create Fields.

So I’m going to call mine fe_id Form Example so I take the FE off and just put _ID, you can call it whatever you want and its going to be the primary identifier and it’s a serial type which means it’s going to basically auto increment for you starting at 1 Auto Increment up of course, we want it unsigned, we want it not null so we can keep all that and we’re going to go ahead and define an Integer Field, we’ll call this my_number and we’ll just say a field for storing an Integer Number, we want an integer; let’s say we want it unsigned, not null, we can leave all the stuff blank. We can create a VarChar field which we can set the link on and this is good for just simple text fields so we’ll call this my_textfield … we’ll just call it my_text and now we’ll go my_text_field because well also create a larger text area here.

Say it’s a field for storing short strings of text and we’ll give it a length of 50, not null, true, we’ll default it to a blank string and take a look at all the different options here; the description for the Database, the type of Field you’re going to be creating … in this example it’s Int which is a storing in number value with a non-decimal number value, the type Var_Char is going to store Text which is just a Variable Character Field, we’re saying the max length on that is 50, we’re saying it to be Not Null, well also do a little bit longer field here; we’ll call this my_text, we want the type on here to be just Text.

Let’s say we want Not Null, to be True and we can just leave it at that. You’ll notice that this is just one big array item that defines this entire Table inside the Fields and the Fields key here we have a whole bunch of sub-arrays. Each one of these is a Field or actually a Column in the Database Table.

After you Define the Fields you can then define some various indexes. You can see there’s a couple here that are examples from the Node Table; let’s say in our example we want to define an Index on the my_number Field, we could go ahead and call this whatever we wanted so we can go ahead and put formexample my_number or you could just put my_number; it’s what you want to call the Index and then in here you define which columns you want to set the Index on. So in this case we want to put an Index on my_number.

If we had any unique keys that we wanted we could define those; in this example we’ll skip it, if we had any foreign keys let’s say we wanted to reference the Node Table and the Node ID or in this case a Node Revision Table, the User’s Table with the User ID, you can take a look at that example; Foreign Keys are just another layer of nested arrays where you specify which Table you’re going to have a foreign key to and which columns are going to map across the Tables, so I’m going to go ahead and get rid of that and our primary key in our example is our serial field here, serial column and it’s fe_id so we’re going to make our primary key fe_id.

So that’s all there is to building a simple hook_schema, like I said; it’s really hard to show all the various options, you really need to look at the Schema API, there’s a whole bunch of example modules out on drupal.org, you can just open one up, open the install File and see how they’re creating the Database Tables and look for all the different examples on the schema api documentation and other Drupal example modules.

So let’s go ahead and come in here and install our Form Example Module, assuming I didn’t mess anything up, things should go smoothly and our module should now be installed, I’m going to hop over to my Database here and I’m going to look for formexample; see if this is going to … and you can see right there I have formexample and you can see there’s an fe_id, my_number, my_textfield in the text, very simple, not a lot to it but it is rather confusing if you’re just getting started with Drupal because there’s a lot of nested arrays inside that hook_schema but once you get the hang of it it really is fairly intuitive and it makes sense on how to build Database Tables inside your module.

When you uninstall this module Drupal will go ahead and uninstall any Tables that you’re module created. So after you disable it and then … okay I’ll step back; once you disable the module, we’ll go over a few things very quickly and I come through here and disable the module and I save this and I refresh this, you’ll noticed that my Form Example Table is still here, however; in order to actually get it to completely go away and destroy all of the data in that Table and drop the Table from the Database I have to physically uninstall it.

Once I uninstall that module that Database will be dropped or that Database Table will be dropped and none of that Database Data will exist anymore. So that’s it for today, hope you learned a little bit on how to create Database Tables inside your Drupal module, we’ll continue next time with Form API example and actually populate some data into this Table. As always you can follow me on Twitter @smthomas3, thanks for watching the Daily Dose of Drupal.