INTRO: Welcome to Apply Filters, the podcast all about WordPress development. Now here’s your hosts, Pippin Williamson and Brad Touesnard.

PIPPIN: Welcome back to Apply Filters, Episode 48. Today’s episode, as usual, is sponsored by the WP Ninjas, the creators of Ninja Forms. They do a lot of awesome stuff over there, so go check them out.

Today, Brad and I are going to be talking about custom tables. This is something that we’ve talked about quite a bit in the past, but we’re going to go a little bit further because I think it’s a very important subject.

Before we dive into custom tables, though, Brad, why don’t you give us an update on some of your recent projects?

BRAD: Yeah, sure. I started promoting WP Offload S3, finally. We’ve had it launched kind of quietly for a month because it was the end of August. We didn’t want to promote it while people were on vacation. And so, yeah, we started blogging about it and stuff.

I’ve been emailing with potential customers a lot that were confused about our pricing because we’re pricing based on the size of the media library instead of per number of sites, like most WordPress plugins do. There’s a little bit of education that’s required there. We do have all the answers in the FAQ. But, as you know, people don’t always read the FAQ.

PIPPIN: Yeah, people don’t read the FAQs.

BRAD: What they do, they send us an email, and then I send them a link to the FAQ in a very polite way.

PIPPIN: My favorite aspect of people not reading FAQs is that, if you don’t have them, you get bitched and moaned at for not having FAQs.

BRAD: Right.

PIPPIN: But, when you have them, they’re not read.

BRAD: Right, right.

PIPPIN: It’s impossible to please.

BRAD: I have never done a heat map of the pricing page with the FAQs on them, but I betcha people do read them, right?

PIPPIN: Absolutely. We know that whether it’s people complaining that you don’t have FAQs or people that didn’t read FAQs, most of those are probably outliers.

BRAD: Yeah, exactly.

PIPPIN: Let me be clear. I am not saying that we should not have FAQs. I think this is very important.

BRAD: Right. One thing I realized, just preparing this episode, is that I never really explained what WP Offload S3, what we’re selling with the upgrade, the Pro version, or whatever you want to call it. One of the things in the free version that’s missing is the ability to upload your existing media library. If you’re just starting a site that’s empty, then it’s fine. You just use a free plugin and start adding items to your media library, and they’ll get sent to S3. But, if you have an existing site with thousands of media library items already, you have to upload those to S3 somehow. The pro version has a tool to do that, and we’ve taken great pains to make it robust enough to handle massive media libraries.

PIPPIN: That’s awesome.

BRAD: Yeah, and so one of the things that it does is find and replace URLs and content as it uploads stuff to the media library. Let’s say you have a media library with 100,000 items, which is not uncommon. Maybe you have 10,000 posts, let’s say, in your posts of content, blog posts, or whatever. Well, after it uploads to S3, that file, it queues it up, the find and replace, because you can imagine how long it would take to upload 100,000 media library items if you were also running a find and replace on 10,000 posts, and for each one of them.

PIPPIN: Yeah. That’s not going to work very well.

BRAD: Yeah, so we’re dealing with some pretty massive scaling issues throughout this project. That’s one of the reasons it took so long to hammer out properly. Those are two major things in the pro version.

You can control your files in S3 from the media library. On each item in the media library, you can copy it to S3. There’s a link or a button for that. You can remove it from S3 if it’s already up there. If you copy it to S3 already, and you’ve removed it from your local server, there’s a function to copy it down from S3 and back onto your local server.

PIPPIN: That’s great.

BRAD: Yeah.

PIPPIN: How well does it integrate with plugins that work with the media library? For example, you have a plugin that either adds or removes items from the media library using some of the core functions inside of WordPress. Does it pick up on those?

BRAD: Yeah, it works perfectly with those plugins. We love those plugins.

PIPPIN: Awesome.

BRAD: What we don’t love is the plugins that create their own functions to upload files to WordPress, but not into the media library, or they add them to the media library in some weird way that doesn’t use the core functions, and they don’t fire the hooks. Then people expect our plugin, of course, to work with it. That’s just not realistic. We can’t cover every plugin that does their own thing, right?

What we’ve decided is, for those cases, if there are enough requests from customers, for example, NextGen Gallery. It’s hugely popular, and it uses its own system for managing the galleries of images. We’re going to do an add-on for that that will send those files to S3 and serve them from S3 within WordPress and on the front-end of the site. That’ll have to be custom, a custom add-on that hooks into NextGen’s hooks because they do their own thing.

Yeah, we can’t. It’s not going to work for every situation, but we’ve done some funny things to make it work in most situations.

PIPPIN: It’s really the best you can do.

BRAD: Yeah.

PIPPIN: When you find a new situation, you see if you can account for it, but there are so many vast ways of doing things. It sounds like you’ve already covered the primary basis.

BRAD: Yeah. Yeah, I think so. Ian came up with a really cool little hack. I wouldn’t call it a hack. I don’t know. You be the judge what it is.

We’ve had plugins. A lot of plugins expect the local file to exist. We have an option to remove the local file once the file has been uploaded to S3. Just wipe it from the server. But, a lot of plugins expect that file to be on the local server to whatever, to manipulate it or do whatever, right?

PIPPIN: Yep. I’ve done that in a plugin.

BRAD: Sure. Ian was trying to figure out a way to satisfy all those situations. What he did is he built a stream wrapper. In PHP, there’s this concept called stream wrappers. You can create–

I’m probably going to botch this explanation, but basically you know how, in PHP, when you file? What’s the function: file_get_content? That function.

PIPPIN: Yeah.

BRAD: You can add a URL in there or a local file, like a file on the local file system or different protocols. You can register your own protocols in there. What we’ve done, we’ve registered S3 as a protocol. When you use the file system functions, it’ll replace the path to the file, the local file, with the path to S3, with the path to the file.

PIPPIN: Wow! That’s awesome!

BRAD: Yeah.

PIPPIN: That’s super smart.

BRAD: It’ll actually just download that file, stream it down to the server, and then do whatever it needs to do with it: open it or whatever. I thought that was a pretty clever little solution.

PIPPIN: Yeah, that’s super clever.

BRAD: I’m pretty impressed with that. Yeah, some other stuff is just add-ons. We’ve got an assets add-on that’ll upload all your CSS, JS, images, fonts, all that stuff from themes and plugins to S3 and start serving them from there.

We’ve got a WooCommerce add-on that’ll allow you to serve your downloads in WooCommerce from S3. It’ll upload them and then set the file as private and then allow you to serve it from there with a secure URL.

And we have the same, very same or similar functionality with Easy Digital Downloads. We have an Easy Digital Downloads add-on.

PIPPIN: Hurray!

BRAD: Which you may be familiar with.

PIPPIN: I’ve used it once or twice.

BRAD: Yeah, and so we’re planning on having more add-ons, like I said, to do similar things.

PIPPIN: It’s very cool.

BRAD: Yeah.

PIPPIN: Now that you guys have this out, is there a potential that it will become maybe on par with WP Migrate DB Pro in terms of what your focus is? Is it going to be a side project? Is it going to become the primary project, or somewhere in between?

BRAD: Yeah, interesting. I don’t know. That is a tough one to answer because I don’t feel like we have–

PIPPIN: It’s too new.

BRAD: That and I guess my feeling is that we don’t really — the plans for the future is to not have any flagship product. It’s just to have a bunch of successful products. That’s what I would love, right? But it may be that Migrate DB Pro will always be our kind of baby or our flagship product, and all the others will be kind of in its shadow or something.

PIPPIN: Even if it’s not officially your flagship product, let’s be honest, it’s the one that really puts you guys on the map for creating these really amazing plugins that kind of go way up and above and beyond what we’re kind of used to seeing with some of these. Really, it has really advanced tools for not just developers, but for sites. Just as an example, there are tons of plugins that would allow you to export a database, but to do it in the way that WP Migrate DB Pro does is so much more polished and refined. Anyway, props on that.

BRAD: Thanks, man. That’s very kind of you to say.

PIPPIN: I’m very excited to see the product launched, and I wish that I had a site that really justified using it. I don’t actually use S3 for any of my sites.

BRAD: Right.

PIPPIN: But I’ve been tempted to just to play with it.

BRAD: Yeah. To answer the rest of that question, the plan right now is to assign two full-time developers to each project to continue to push it forward. That’s kind of the long-term plan.

PIPPIN: Yeah, I think that’s something we pretty much have to do as companies get more products under their belt because otherwise you just get ones that they either lose focus, don’t get as much attention. I’ve definitely found that with Affiliate WP, EDD, and Restrict Content Pro. Aside from myself, we have five people that work full time on EDD. We have one that works full time on Affiliate WP. For that reason, EDD gets a lot of the focus.

BRAD: Right, I see. Yeah.

PIPPIN: But I think, as we grow, I think we’re going to get another developer, for example, that does nothing but work on Affiliate WP or, like, 90%.

BRAD: Yeah, I think that makes sense to give it a little bit more of a push forward. Yeah.

PIPPIN: Yeah.

BRAD: What have you been up to?

PIPPIN: Well, WordCamp South Africa was this last week, and my family and I flew to South Africa for a weeklong vacation/WordCamp, which was awesome. I’ve got to say, South Africa is a beautiful, beautiful country, and we were in Capetown, which was where WordCamp was. It’s just an awesome city, if anybody is considering going or looking for somewhere to go, put that one on the map. It’s definitely worth it.

Depending on where you are in the world, it’s a few long flight. I think we had–

BRAD: I don’t think it depends where you are from. I think, for most people, it’s a long flight – for most people.

PIPPIN: If you are anywhere in Europe, North or South America or, say, Australia, it’s going to be a really long flight. Actually, most of Asia will be a really long flight as well. It was about 31 hours of travel time to get there for us and about 28 hours to get back. Which, if you’ve ever traveled with a toddler and a one-year-old, it’s a little bit longer than 31 hours in terms of how it feels like.

BRAD: Yeah, it’s a good thing you spent the week. You mostly bounced around Capetown, or did you get out of the town?

PIPPIN: Yeah, we spent almost all the time in Capetown. Well, with one day we traveled down to Cape’s Point, which is the southern most point of Africa, which is just absolutely beautiful. It’s right there by the Cape of Good Hope.

Anyway, we had WordCamp South Africa, which was there. We had a mini vacation and really didn’t do a whole lot while I was there in terms of work-wise. But, as soon as I got back, then it’s full steam ahead on getting two major releases pushed out. Affiliate WP 1.7 is getting wrapped up, hopefully today, actually. After I jump off this episode, I’m going to see if I can finish up the release and wait for Mr. Andrew to wake up–since he’s in New Zealand, he’s currently sleeping–so he can test all my changes.

Then, Restrict Content Pro 2.3 is slated for release. I was hoping for it today, but probably won’t happen. It’s got a little bit more work to do on it, so that’s been kind of my focus for the last couple of days, along with a pretty significant EDD point release that we pushed out actually while I was flying over the Atlantic on the way home.

BRAD: Wow!

PIPPIN: Which was awesome.

BRAD: The times we live in.

PIPPIN: Yeah. Awesome, right? Yeah, that’s been pretty much my focus for the last week or so.

BRAD: Did you give a talk at WordCamp?

PIPPIN: We did. Yeah, I did two presentations. One was a panel. We had a panel for e-commerce. It was myself, Justin Sainton, Mark Forrester from WooThemes, and the head of development or product development at PayFast, which is the primary South African payment gateway. We gave a panel on e-commerce, which was fun. There’s a lot of insightful commentary there. Then I gave a presentation or a workshop on backwards compatibility as well.

BRAD: Nice. By workshop, do you mean was it longer than a regular session?

PIPPIN: It was. WordCamp sessions usually vary somewhere between 20 to 45 minutes. For this one, we had a full hour from start to finish. Instead of there being 45 minutes or an hour between sessions, there is an hour and a half or an hour and 15 between sessions, which gave us — so we can talk for the entire hour, which was awesome. It let us go a little bit more in depth. With something like backwards compatibility, you really need to get in depth to be super valuable. So, we were able to do that, which was fun.

BRAD: Nice. Nice.

PIPPIN: Yeah.

BRAD: Did you do some live coding there too? Did you code?

PIPPIN: I didn’t do any live coding, but I showed a couple of examples. Honestly, I think a backwards compatibility workshop is something that I would love to do a five-hour workshop on so you could actually dive in, do some real coding, and problem solving. But for even an hour, you’re still kind of limited on just how much you can show.

BRAD: Right. Gotcha.

PIPPIN: Should we jump into this?

BRAD: Yeah, sure. I guess we should preface this talk about custom tables that we’ve already talked about custom tables quite a bit before. Back in Episode 12, we talked a lot about custom tables, like when you should use them and when you shouldn’t use them. Yeah, that whole episode is about custom tables, so check that out.

Episode 32, we talked a bit about upgrade routines, and you were talking about your transitioning to custom tables, I think, in Affiliate WP and EDD, or maybe not Affiliate WP. Anyway, you were talking about custom tables and those products.

PIPPIN: Yes.

BRAD: Yeah, check those two past episodes out. I think they’ll give you a good kind of intro to custom tables. We’ve also had some blog posts. I’ve just blogged recently. I gave out some kind of old code, some code that I wrote between three and four years ago that kind of helps manage custom tables a little bit.

You’ve been doing a blog post series, haven’t you, Pippin?

PIPPIN: Yeah, I just started one two weeks ago, I think, and it’s going to be a whole series on customized tables. The premise of it is not just about custom tables, but it’s the process of building a database abstraction layer for your plugins, so a complete API. It’ll be going through building a series of classes, building a class for each individual table, building out the API that allows you to interface with your database tables very easily.

I’ve only actually written the intro post to it, but I’m hoping to get the next post written some time later this week or early next. It should be about a five- to ten-part series once I’m done, which means it’ll take, like, five years to finish.

BRAD: Right. In your intro post, you’ve made some really good points about when you use custom tables, you have a lot of responsibilities with regards to certain things because you’re no longer using WordPress’s API. WordPress kind of takes care of a lot of the important things, so you have to be aware. Maybe you want to cover a few of those things, just kind of mention what they are.

PIPPIN: Yeah. One of the nice things about relying on WordPress APIs for things like, for example, working with the WP post table or the post meta, comments, or comment meta, users, user meta, etc. is that WordPress handles several very important aspects of it, one of those being caching. When you do something like a WP query, WordPress is caching those results. And so, if you run the same query five times on a page, you’re really only running it once because you run it, it caches it. Any other time you run it, WordPress is pulling directly from cache. This is a very, very important aspect of working with a database.

When you’re using the WordPress core APIs or functions, that’s all handled for you. When you have a custom table, that’s not automatically handled for you. And so, it is our responsibility as developers that are building custom tables to do that. We have to build it ourselves. That’s one of the things that this tutorial series that I’m slowly working on is going to help cover.

Another big one, outside of caching, is security. Let’s say that you call update_post_meta(), the function in WordPress, and you pass it on a ray of information. WordPress core actually takes care of some of the sanitization of that data to ensure that you’re not putting something into the database that’s going to be harmful. And so, a lot of times you can, right or wrong, get away with actually sending un-sanitized, potentially dangerous data directly into the WordPress helper functions because it handles it for you.

BRAD: What do you mean? What would be an example of something dangerous that you could pass in?

PIPPIN: Un-escaped data, for example.

BRAD: Oh, right, like JavaScript.

PIPPIN: Sure.

BRAD: That would allow a cross-site scripting attack or something like that.

PIPPIN: Yep, lots of different things like that.

BRAD: Right.

PIPPIN: And so a lot of the WordPress core helper functions take care of it, so they don’t let you do it, even if you tried to or if you just didn’t realize you needed to. When you are building your own tables in your own API to work with your custom tables, this isn’t handled for you automatically, and so paying attention to security issues is really, really important. You don’t want to suddenly realize that you have either cross-site scripting vulnerabilities or SQL injection. These are the types of things that you have to pay a lot of attention to when building custom tables.

PIPPIN: There’s actually a third point as well. Brad, I’ll let you touch on this, but it’s about maintenance. How do you maintain custom tables is something that’s definitely an issue as well.

BRAD: Yeah. I think these are all really good points. I guess there’s a lot more–for lack of a better term–responsibility. You have a lot more things to worry about because WordPress core handles so much of this on its own. You have to be constantly kind of auditing the code, probably, making sure that things are done in a secure way, that things are cached properly, that you don’t have performance bottlenecks.

And so, that requires constant maintenance into the future, right? You constantly have to be maintaining that code and making sure that it’s secure and performant. That’s the kind of downside of custom tables is that it is more work.

But, I think we’ve covered in the previous episodes of this podcast, and in your blog posts I think you cover some pretty huge upsides as well, though, too.

PIPPIN: Yeah, I think there are definitely some big ones. Yeah, I think we’ve covered those pretty well. If anybody is not sure on some of those upsides, feel free to leave a comment here, and we can either link you to references or get into a conversation about some of the more reasons for using custom tables as well.

You recently wrote a blog post about custom tables that was really great that you mentioned a little bit ago. In there, you included an example class, which you call your WP AS Model for a database layer.

BRAD: Yeah, sure. It’s just an abstract class that has functions like get, insert, update, delete, just a typically CRUD class like C-R-U-D. What does it stand for? Create, read, update, and delete, I think: CRUD.

PIPPIN: Yes, I believe so.

BRAD: There are a few different ways to say; I think there are a few different terms for this kind of a class. Anyway, all the functions are static. Basically, you would extend that model class with, let’s say, a class called WPS model order or order meta, for example. You could use that. You could call those functions statically. You could call get and then provide the ID for the row that you want to get, like get that order ID, get the data for that order. That’s basically it.

Like I said, I wrote it three years ago, three or four years ago, and it did the job at the time. But I think, if I were to do it over again, I would definitely do it differently. I’d probably use the factory design pattern or probably do something like what you’ve done in EDD, Pippin, and just kind of basic instance classes.

PIPPIN: Sure. I want to inject something real quick for anybody listening and you would like to view this, to kind of get a better idea of what we’re talking about with these classes, we have links to them in the show notes. If you are able to look at a screen while listening, go pop that open, and it will help give you a little bit more context.

BRAD: Yeah, exactly. It’s hard to kind of explain what code. I mean code speaks for itself than anyone could speak for it, I think, right?

PIPPIN: Absolutely.

BRAD: One of the things that it does, it just returns an array of data. I find it would be better if it returned an object with properties instead, and maybe some methods. I would probably use object classes instead of the static way of doing it.

PIPPIN: Regardless of some of the little nuances, like returning an array versus an object, making it static or not, I think it’s important to kind of look at what the purpose in this class is. It’s basically a foundation for how you interact with your custom tables, a very simple class that you can then extend or reuse over and over again to make reading, updating, deleting, inserting, etc., a really nice, simple task. Every time that you want to go update something in your DB, you’re not writing an SQL statement. You’re simply calling your helper function.

BRAD: Exactly. Yeah. Yeah, I mean that’s kind of the database layer of your application. It’s to keep kind of all the queries in one place. I have to say I’ve seen that fairly rarely in the WordPress plugin space. It seems like there’s very rarely a database layer to plugins. You’ve looked at a lot more plugins than I have, so what would you say?

PIPPIN: I’d second that. I think it’s pretty rare, and I think it’s rare for two reasons. One is that not that many plugins, if we look at the shear number of them, actually use custom tables. Even plugins that could or maybe should use them don’t. We’ve had to ignore plugins that just have no reason to use a table at all. Out of the plugins that would have a reason to use a table, not that high percentage of them do. Of those that do, very few of them actually have abstraction layers for their database, which is unfortunate because I think the very first time that you provide another extraction layer and then you use it, you’ll realize just how awesome it is to have one.

BRAD: Yeah. Well, and it simplifies the rest of your code where kind of the logic of your application is.

PIPPIN: Right.

BRAD: That’s the stuff that, if you have queries mixed in with your logic that’s doing some calculations or something, it just adds to the noise. It just makes it harder to read that logic.

PIPPIN: It’d make it much harder to debug, for sure. Let’s say for example you have an update process for something in your database and it’s failing. If you have your logic separated from your queries, it’s a lot easier to figure out which one of those is failing and then break it down and go from there.

BRAD: Yeah, exactly. WordPress core, to its credit, has a database layer, pretty much, right?

PIPPIN: It does.

BRAD: Actually, does it? It kind of mixes it up a little bit.

PIPPIN: It does to a degree. Like the WP DB class is a database layer, but it doesn’t necessarily provide some of the helper functions that yours does or the ones that I wrote and EDD and Affiliate WP do. It kind of provides a simple way to process, to run some SQL queries, but does not give you some premade queries to run. For example, your class and the ones that I wrote have a get_row() or get_column() methods, for example. You simply say this is the row ID from this table that I want, or this is the row and the column name that I want.

WordPress core does not have that. They do have some helper functions that help that. This is kind of going another layer, another step further.

BRAD: Right. Yeah. I’m just trying to think. It definitely doesn’t have a distinct data layer. You know what I mean? There’s not a folder called DB that has nice classes for each of the tables or anything like that. It is kind of mixed in with the other functions still. Yeah, that could probably be cleaned up over time, over many years.

PIPPIN: I’ll let you write that patch for that one.

BRAD: Yeah, I think it’s going to be a lot of patches over many years. But I have a question for you about EDD. If you were doing EDD over today, would you use a custom post type for orders, or would you do something else like maybe custom table for orders?

PIPPIN: It would have a custom table, no doubt about it.

BRAD: Really? No kidding.

PIPPIN: There’s literally zero doubt.

BRAD: I would guess — I mean just like the order itself, so not the extended metadata or anything, just the order.

PIPPIN: The order itself would be a custom table, absolutely.

BRAD: Wow! I’m shocked because I was going to guess that you would still use a custom post type.

PIPPIN: No, no, no.

BRAD: Awesome. Okay.

PIPPIN: If I was to pick one regret from building EDD, it’s that one right there. I wish that had never been in a custom post type. There are a lot of reasons for it. I touched on a couple of them, I think, in my blog post about custom tables, in the intro post for the series. I’ll probably touch on it more, but it comes down to a few things.

The biggest one for me is efficiency. The way that we store orders right now in EDD, they’re stored as a custom post type. Then, all of the order data or the majority of the order data is stored as post meta.

Just as a really quick analysis, let’s say that you have one order. On that order you have various fields. You have a total. You have the amount in taxes. You have the amount in discounts. You have a billing address, which is going to be six fields: line one, line two, city, state, province, country, postal code — seven fields, sorry. No, six fields. I can’t count.

Now, not necessarily for EDD, but in most … you would also then have a shipping address, which would be another six fields. You’re going to have things like a customer ID, which would come from a customer’s table, probably. You’re going to have a status for the purchase, perhaps a purchase method (PayPal, Stripe, etc.), IP address, date.

BRAD: Product ID.

PIPPIN: Product IDs, which would probably be a separate table as well because you might have multiple product IDs. But in some way or other, you would be relating it to product IDs, so in this case it would probably be relating to a row ID in the product in sales log column or something, or sales log table, something like that. That’s kind of at a minimum.

You’re looking at somewhere between 10 and 20 fields, at minimum, for an order record. Right now, every single one of those in EDD is stored as a separate piece of post meta, which means that we have one row in the database that is the custom post type entry, and it holds a little bit of information, things like customer name, title, a date, post type, and a status. That’s really about it. Out of the, I think, 18 or so fields that a WP post table uses or has, we’re using 5 of them, I think, which is kind of crazy.

We have one row for the main order, and then we have 10 to 20 rows in post meta for all of the order information. We’re using 20+ rows for a single record for an order in the database. To me, this is absolutely asinine.

BRAD: Yeah.

PIPPIN: This is nuts.

BRAD: Yeah.

PIPPIN: The biggest reason for me is efficiency. If you’re storing stuff in post meta, sure, it works great. It’s super easy to do. It doesn’t mean that it’s better. In our case, it’d be far more efficient to store it in a custom table.

BRAD: I guess what I was thinking is that, sure, you could have a custom table for all the order meta, so instead of having 20 rows of order meta in the post meta table, you would have one row in order meta table with proper field types for each piece of data. But you’d still use the custom post type for the object of an order just so that, in the WordPress dashboard, it would take advantage of all the things, all the goodies that come with the custom post type.

PIPPIN: Yeah, and there’s some definite truth to how that could work. Honestly, if we decide to move, that might be the way that we go is only moving the metadata. I don’t know the answer to that at the moment.

My initial thought is no. If we’re going to move it, we’re going to move it all because, if we move it all, there’s a ton of queries. And so, let’s say we have an orders table and an order meta table. The vast majority of the data would go directly into the orders table and a little bit would go into order meta. But, if we had the majority of the stuff in orders, the number of queries that we can run, the kinds of queries we can run are exponentially greater than they are right now.

Right now for us to run calculation queries for reports is painful because it’s separated across the post meta and multiple rows in post meta. As an example, have fun trying to come up with the queries to get you earnings for this month, gross earnings minus taxes minus discounts minus refunds, things like that. Those kinds of queries are painfully difficult and slow to write right now because, like taxes, total, subtotal, discounts, refunds, those exist over the course of, like, 10 to 15 rows per order, which is very difficult to query – efficiently, anyway.

Yeah, I would. To get back to your question, I would absolutely do it in a custom table. There’s actually a very distinct possibility that, in the next two years, we will move EDD’s payments to a custom table, which will be a pretty painful process, but we’re going to try and make it happen. That’s my hope.

BRAD: It won’t be that bad. It sounds worse than it is, I’m sure.

PIPPIN: I’m not convinced of that.

BRAD: Did you want to talk about dbDelta() right quick?

PIPPIN: Sure. Maybe as a quick intro to anybody who is not super familiar with building custom tables in your plugins, one of the first things you should go look at is the function called dbDelta(), which is a function provided by WordPress core and is a super handy helper function for creating tables, updating tables and, yes, basically it allows you to pass SQL, create syntax, and it will create a table.

But it also has a really nice feature of it as well that, let’s say that you, in version 1 of your plugin, your table is a particular schema. Then, in version 2, you need to change that schema. Usually what you would do is you would write an SQL alter statement to alter a table. Well, dbDelta(), what you can do is you can pass it the same SQL_create() statement, but with your adjustments for your new rows and column types, whatever changes you’ve made. And, dbDelta() will just automatically create the alter table syntax to update your table, which is super handy.

BRAD: Yeah, very cool.

PIPPIN: It’s a super handy function.

BRAD: We use it in our plugins as well, yeah, because you can just have the dbDelta() function sitting your code and just modify that create_table() statement. When you modify it, the next time the plugin version goes out and the dbDelta() is run, it’ll just magically detect that change and update that table.

PIPPIN: Yeah, it’s awesome. We use it. Affiliate WP has table changes just about every major version because, as we’re either adding data, altering data, or something like that, we have a generic upgrade routine that we run. If we had changes to our database, all we do is just pass our create_table() statement to it and we’re done.

BRAD: Yeah. I guess I shouldn’t make it sound too magical. You do have to version your database still, right? You don’t want to be running that on every page load or anything like that.

PIPPIN: Well, yeah. You should only be doing it, whether you’re versioning your database or not, which you should be. You should only be running it during the install process or an upgrade process. Don’t run it on every page load. Oh, please no.

BRAD: Right.

PIPPIN: I don’t actually know how much of an effect it would have, but it’s just: Why would you do that?

BRAD: Yeah. The upgrade process is the part that requires you to reversion your database.

PIPPIN: Right.

BRAD: For example, what we do, we keep a DB version setting in the options table, like DB version for whatever plugin. Then if that version is less than the current version hard coded into the plugin, then run the dbDelta() function or something like that.

PIPPIN: Yep, we do the same, and we keep a version for each of our tables. In Affiliate WP, for example, we have four tables, and so each table is versioned.

BRAD: Wow!

PIPPIN: Along with the main plugin version.

BRAD: Nice. That’s neat.

PIPPIN: I don’t know if that’s good or bad. I think there are upsides to having one version number for the table, for all of the tables, or having a separate one, but we chose to do it as separate.

BRAD: Yeah, I don’t know. I think WordPress just does one. I think it has one version.

PIPPIN: Yeah, I’m pretty sure it does.

BRAD: But anyways, yeah, I’m sure there are negatives and positives of that. I had a look. You were showing me the EDD database classes. Are those released yet?

PIPPIN: Yes. Yep, those are live.

BRAD: Those are live? Okay.

PIPPIN: I can tell you in a moment. We have a couple of database classes in EDD. In version 2.1, we introduced a customer’s table. Previously, we didn’t really have a true definition of what a customer was in the database. If you were a customer that purchased while you were logged into a WordPress account, you were tied to a user. If you purchased as a guest, your customer account was nothing more than your email address. There was some weird, poor planning done there.

In version 2.1, we introduced an actual customer’s table where every single customer that goes through the system is now stored in this table. It’s been a nice improvement for sure. But, when we did that, we also introduced a generic EDD DB class, which is the same kind of base class that we talked about earlier that’s in your blog post and that I’m going to get into in my blog series, but is just a base class for interacting with our database or with our tables. It provides the helper functions to get a specific row, to get a specific row based on a column/value, to retrieve a specific column, or to get a specific column by a particular value.

Let’s say that you want the customer ID, and you want to find that based upon their email address. You would say get column by ID or get the column ID where the email equals this value.

BRAD: Right.

PIPPIN: We have our insert, delete, and update statements and all those helpers.

BRAD: You just extend that generic DB class with whatever object, database object.

PIPPIN: Correct. Right.

BRAD: You have a customer database class as well that extends that generic one.

PIPPIN: That’s correct.

BRAD: Yeah, and then, inside that, you have functions, special functions that are more specific to that object, I guess, too.

PIPPIN: Yeah, so we have a couple things. One, our generic class lets you set up the columns and default values for each column in the database. Our columns for the customer’s table is a user ID, an email, a name, payment IDs, value, purchase count, notes, and date created. We can set up those default values so that when we create a new customer, we know what the defaults are going to be in there.

Then, our generic class has an insert function, but then our customer’s class has an add function as well or an add method, which simply calls the insert. The reason we do this is so that we can put in additional hooks. We can do some data manipulation, whatever we want. When we want to create a new customer, we just say, “EDD customer’s DB add,” and then it does that stuff.

The same thing, we have a delete method that’s specific to the customers. We have things for updating individual columns in a customer database. We have one for retrieving. You can call, like, say get_customer_by, and then you can say I want a customer. Here is the email address, and so I’m going to get them from their email address. We have all these different helper methods that can be very handy.

BRAD: Right. I don’t see any caching in the database stuff. How are you handling that?

PIPPIN: Yeah, so this is a really good observation. I like that you asked this question because, if we look at yours, it’s the same way.

BRAD: Yeah, exactly.

PIPPIN: Brad’s playing with us here. Really, what we have here in the basic EDD DB class, and if you look at Brad’s abstract class as well, you’re going to see that none of the query functions, whether we’re talking about get, get_column, get_row, etc., none of them have caching on them. They are direct database queries, and they’re returning the results. There’s no caching. There’s no anything on them.

If you look at that, you could first think, “Well, that’s not very good because, if we’re querying customers over and over again, or we’re querying whatever the record is, we need to make sure there’s caching here because that’s one of the fundamental aspects of a database layer, or one of the things that you should be doing when you’re writing plugins that do a lot of data.”

The way that we’ve done it is, our basic abstract class has no caching on it because they are meant for the raw queries that are happening. Then when we extend the class, say for like our EDD customers DB, and we will have a wrapper method in there, that wrapper method will handle the caching.

As an example, the abstract class has a get method, which allows you to get a specific row. In this case it doesn’t, but we would also have a way of getting a set of rows. Let’s say we want to get all customers that match this or that, or we just want to get the latest 20 customers. What we do is we actually build in a wrapper method into the specific classes that extend the base. Those will have caching on them.

For us, we have a customer’s class that has a method called get_customers() that then kind of functions like, say, WP Query where we can pass in the number that we want, the offset for things like pagination, the order, the order by, a search parameter, or anything like that. That method sets up all of the logic for doing the caching and the order bys and all that stuff. Then it calls a helper method, but that one is cached. It was kind of an extended explanation, but basically the base class is for raw queries and then each of the classes that are actually used wrap those base methods with caching.

BRAD: Oh, I see. I see. What about, have you ever considered having a global function called get_customers() and doing the caching in there, or are you just kind of done with global functions?

PIPPIN: We do it sometimes. Most of the time when we have global functions, it’s because they started out global and then we introduced an object later, and so we just left the globals there for backwards compatibility. We typically don’t introduce globals now, at least for this kind of stuff. But what we do do is we actually make, like the customer’s database, for example. You could actually access it kind of in a way that you would a global function. The reason that’s possible is because of the way that the Easy Digital Downloads primary object is set up.

It’s set up as a singleton. What you can do and, on it, it has properties for each of our databases. So, you can say, “EDD customers get customers,” and you can call it kind of like a global function, which makes things really easy.

BRAD: Yeah, that’s nice. I like that pattern. That’s pretty sweet.

PIPPIN: Yeah, it’s worked pretty well.

BRAD: Yeah. I guess we should probably wrap it up. I just want to mention Big Snow Tiny Conf again. It’s a conference where you can talk about business and ski or snowboard, January 25th to 28th. It’s coming up. We’re going to start selling tickets in a couple weeks. Go to BigSnowTinyConf.com and sign up to our mailing list if you’re interested in that.

PIPPIN: You should definitely go and make me jealous because I would love to go, but I can’t this year.

BRAD: There you go.

PIPPIN: It sounds awesome.

BRAD: An extra incentive to make it.

PIPPIN: Make me jealous.

BRAD: Then, of course, as always, we really appreciate iTunes reviews, so if you haven’t already, yeah, hop on over to iTunes and give us a five-star review. That would be awesome.

PIPPIN: If you have any feedback at any time, let us know. We got some feedback recently that’s kind of helped us get some good insights to how we’re doing. If you have any comments–good, bad, or ugly–let us know.

BRAD: Absolutely. Talk to you next time.

PIPPIN: Thanks, everybody.