For WordPress 2.4, I propose that we add a general meta-data table. It would be similar in structure to the current postmeta and usermeta tables, except with an additional field specifying the domain of the meta-data.
There are at least a couple of benefits to such a table:
- We could have meta-data for comments and categories. Others have proposed meta tables for the comments [1]; rather than adding meta tables seriatim, a general meta-data table would meet that need and offer the possibility of actually reducing the total number of tables.
- It would provide a better place for plugins to store data. Currently, most plugins---when they don't have enough data to justify creating their own table---store data in the options table. I think there are disadvantages to bloating the options table, one of which is that WP loads almost all options into the object cache.
meta_id is a unique identifer for each meta entry. object_id is the id of whatever it links to (post, comment, whatever) meta_type defines what type of thing it's for (post, comment, whatever). key and value should be obvious.
On 10/4/07, Andy Skelton <skelt...@gmail.com> wrote:
> I like it. A good starting point would be a CREATE TABLE statement. > Would you post it here when you've got it designed?
Also note that this one table could replace both the postmeta and the options tables. If you consider the "blog" as a possible object with an object_id (instead of a blog_id), that is. Might need an autoload column to be fully compatible, I suppose.
> meta_id is a unique identifer for each meta entry. > object_id is the id of whatever it links to (post, comment, whatever) > meta_type defines what type of thing it's for (post, comment, whatever). > key and value should be obvious.
> On 10/4/07, Andy Skelton <skelt...@gmail.com> wrote: > > I like it. A good starting point would be a CREATE TABLE statement. > > Would you post it here when you've got it designed?
I don't think putting the options there would be a good idea.
many users understand the options table and know how to change values there manually.
putting it into a generic metadata table will make wordpress much more opaque to users.
Otto wrote: > Also note that this one table could replace both the postmeta and the > options tables. If you consider the "blog" as a possible object with > an object_id (instead of a blog_id), that is. Might need an autoload > column to be fully compatible, I suppose.
> Also note that this one table could replace both the postmeta and the > options tables. If you consider the "blog" as a possible object with > an object_id (instead of a blog_id), that is. Might need an autoload > column to be fully compatible, I suppose.
At first glance, this does not look like a good move. It would seem to confuse things.
However, if you consider the blog as an object with the implicit ID of 1, it makes perfect sense. Taking it further, into the context of WPMU, there is a blogs table where each blog has an ID and a sites table where each site has an ID.
Here are the items that would be rolled into a unified meta table:
> I don't think putting the options there would be a good idea.
> many users understand the options table and know how to change values > there manually.
> putting it into a generic metadata table will make wordpress much more > opaque to users.
Bah. Good database design waits not for users manually editing things! Plugins will no doubt adapt (or not notice, if they're using the add and remove options functions).
On 10/4/07, Andy Skelton <skelt...@gmail.com> wrote:
> At first glance, this does not look like a good move. It would seem to > confuse things.
> However, if you consider the blog as an object with the implicit ID of > 1, it makes perfect sense. Taking it further, into the context of > WPMU, there is a blogs table where each blog has an ID and a sites > table where each site has an ID.
That was my basic line of thought, yeah. The options table is nothing more than key/value pairs tied to a blog_id. For MU, this also means that options can be fully separated without wacky option naming conventions.
Pretty much any table you care to create is going to have some kind of unique ID field. If it then contains key/value pairs, it can fit into this meta table design, as long as it also has a unique meta_type. The meta_type could even be as simple as the name of the other table that the meta_id is referring to. _______________________________________________ wp-hackers mailing list wp-hack...@lists.automattic.com http://lists.automattic.com/mailman/listinfo/wp-hackers
Besides the missing auto_increment, I would use object_type instead of meta_type.
On 10/4/07, Andy Skelton <skelt...@gmail.com> wrote:
> I'd love to have mediameta and get attachments out of the posts table, > but that's neither here nor there...
I was about to propose the same, but I would make it a files domain (or attachments) not just media, so that you can get access to any kind of file related to a post (or any object), but not tied to them. I think that would make WP even more flexible to be used as a CMS.
> However, if you consider the blog as an object with the implicit ID of > 1, it makes perfect sense. Taking it further, into the context of > WPMU, there is a blogs table where each blog has an ID and a sites > table where each site has an ID.
> Here are the items that would be rolled into a unified meta table:
> I'd love to have mediameta and get attachments out of the posts table, > but that's neither here nor there...
> Andy
The other consideration (if a goal is to have a single, consistent table schema between MU and regular WP - not always the case now but certainly welcome going forward), is that then most of the 'meta' info mentioned above (post-related, comment-related, etc.) would need to be made unique by site, blog and object (post, comment, etc.) id.
Don't know if this is getting too deep into MU territory for this list, but it might also be nice (especially for "mediameta") to be able to share some items across multiple sites and/or blogs, so as to avoid duplication while promoting reuse. In that case, then maybe two tables would be in order; one for the meta data (id, key, value) and one to associate the data in that table to one-to-many other objects without duplicating the meta data itself.
As I typed that, I realize that it might be over engineering things, but might as well throw it out there for discussion.
+1 on getting the attachments out of the post table one way or another, though.
If this will eliminate goofy things such as the table_prefix being embedded in various fieldnames, I'm all for it! :)
Kidding aside, I think this is a good idea. I also kind of like Jared's idea of a table for the meta data itself, and a separate table linking it to the various objects. It's worth consideration, at the least. I think it might lead to some interesting and unanticipated flexibility.
At first blush I was hesitant at the concept of this also including the Options, but... (as Otto points out) so long as this is abstracted behind the add and update options functions, it shouldn't be a big problem for plugin designers.
> For WordPress 2.4, I propose that we add a general meta-data table. > It would be similar in structure to the current postmeta and usermeta > tables, except with an additional field specifying the domain of the > meta-data.
> There are at least a couple of benefits to such a table:
> - We could have meta-data for comments and categories. > Others have proposed meta tables for the comments [1]; rather than > adding meta tables seriatim, a general meta-data table would meet that > need and offer the possibility of actually reducing the total number > of tables.
> - It would provide a better place for plugins to store data. > Currently, most plugins---when they don't have enough data to justify > creating their own table---store data in the options table. I think > there are disadvantages to bloating the options table, one of which is > that WP loads almost all options into the object cache.
> On 10/4/07, Andy Skelton <skelt...@gmail.com> wrote: >> I like it. A good starting point would be a CREATE TABLE statement. >> Would you post it here when you've got it designed?
> I've written up a patch to get it going[1]. I've tested it > successfully, but I'd appreciate it if others would as well.
If it would help, I'll write some unit tests up when I get home. I think the true testament would be to convert existing meta code over. It would give better indication of whether the new code would in fact replace the current tables.
Would it be neat to have a sort constant to register meta type?
On 10/11/07, wordpr...@santosj.name <wordpr...@santosj.name> wrote:
> Also, there seems to be a missing insert_general_meta(); the code in > update_general_meta() will return false if there is no $object_id.
Shouldn't need an insert, as having no $object_id is not an option. Every entry must have an object_id. For the cases where it replaces wp_options (let's call that object_type = 'blogmeta'), the $object_id = some unique identifier to the blog (consider the MU version here too, each blog can have a different object_id).
> On 10/11/07, wordpr...@santosj.name <wordpr...@santosj.name> wrote: > > Also, there seems to be a missing insert_general_meta(); the code in > > update_general_meta() will return false if there is no $object_id.
> Shouldn't need an insert, as having no $object_id is not an option. > Every entry must have an object_id. For the cases where it replaces > wp_options (let's call that object_type = 'blogmeta'), the $object_id > = some unique identifier to the blog (consider the MU version here > too, each blog can have a different object_id).
> Oh, well, yeah, okay we do need something that does an insert. It > would be nice if the update and insert were the same function call > though.
> On 10/11/07, Otto <o...@ottodestruct.com> wrote: > > On 10/11/07, wordpr...@santosj.name <wordpr...@santosj.name> wrote: > > > Also, there seems to be a missing insert_general_meta(); the code in > > > update_general_meta() will return false if there is no $object_id.
> > Shouldn't need an insert, as having no $object_id is not an option. > > Every entry must have an object_id. For the cases where it replaces > > wp_options (let's call that object_type = 'blogmeta'), the $object_id > > = some unique identifier to the blog (consider the MU version here > > too, each blog can have a different object_id).
What about just having save_general_meta(), which would insert if it doesn't exist or update if it does. From your wording (and my lack of reading the entire function), I would assume that the one function does both now. Would need documentation, which should be written, in any case that the function name is not changed. Meaning if I was confused, I would assume others would also.
I didn't know what $object_id was, but it makes sense now. At least I'll be able to write better tests. Me two and a half hours from now: "WTF is this object_id and where can I get its value? Ah hell, I'll just make stuff up."
> Oh, well, yeah, okay we do need something that does an insert. It > would be nice if the update and insert were the same function call > though.
> On 10/11/07, Otto <o...@ottodestruct.com> wrote: >> On 10/11/07, wordpr...@santosj.name <wordpr...@santosj.name> wrote: >> > Also, there seems to be a missing insert_general_meta(); the code in >> > update_general_meta() will return false if there is no $object_id.
>> Shouldn't need an insert, as having no $object_id is not an option. >> Every entry must have an object_id. For the cases where it replaces >> wp_options (let's call that object_type = 'blogmeta'), the $object_id >> = some unique identifier to the blog (consider the MU version here >> too, each blog can have a different object_id).
On 10/5/07, Austin Matzko <if.webs...@gmail.com> wrote:
> For WordPress 2.4, I propose that we add a general meta-data table.
> Thoughts?
Sounds like a crufty mess waiting to happen. If a plugin has data beyond configuration options, it should be creating a new table for it, no matter how small the amount of data is. I'd rather clean up my DB by pruning tables than by sorting through a gross metatable full of random snippets of junk.
The problem here is a matter of educating plugin authors about best practices, and adding a generic "dump stuff here" table is not the answer.
As assumed of using the wp_options table for the dump stuff here? Just run a SQL Delete statement deleting everything that isn't post, etc.Came to think of it, it would be useful to always create a new table, but my plugin uninstalls its data when deactivated. Not hard to implement.
Mike Purvis wrote: > On 10/5/07, Austin Matzko <if.webs...@gmail.com> wrote:
>> For WordPress 2.4, I propose that we add a general meta-data table.
>> Thoughts?
> Sounds like a crufty mess waiting to happen. If a plugin has data > beyond configuration options, it should be creating a new table for > it, no matter how small the amount of data is. I'd rather clean up my > DB by pruning tables than by sorting through a gross metatable full of > random snippets of junk.
> The problem here is a matter of educating plugin authors about best > practices, and adding a generic "dump stuff here" table is not the > answer.
Separate tables: + Cleaner + Easier to simply drop old tables when they're not used anymore - Extra tables means extra joins and/or extra queries - Wordpress won't know about these tables internally and can't handle them itself with things like caching - Every table is going to be of a different layout and such, more confusion - Poor plugins will never do things properly, bad indexes and such = slowdown
Combined table: + Standardized layout makes it simpler for plugins to manipulate correctly + Most plugins just need to store things in the form of key/value pairs anyway + Half a dozen things in WordPress itself would be able to use this table + Wordpress can handle it with caching and such + Less joins, less queries + Proper indexing and good design means it'll be quick and fast - Potential big table full of potential junk - Manual cleanup is harder (although separating things by the object_type makes this simpler)
Probably some I left out. On the whole, I'm in favor of a combined table simply for the reason that the internal bits of WordPress need it. Why have a postmeta, options, commentsmeta, etc? One table can handle these better. We're doing weird stuff in WP too, like attachments as posts and other things that would be better served by good metadata. A refactoring of these is in order, and if a combined meta helps push that along, I'm all for it. _______________________________________________ wp-hackers mailing list wp-hack...@lists.automattic.com http://lists.automattic.com/mailman/listinfo/wp-hackers
> Probably some I left out. On the whole, I'm in favor of a combined > table simply for the reason that the internal bits of WordPress need > it. Why have a postmeta, options, commentsmeta, etc? One table can > handle these better. We're doing weird stuff in WP too, like > attachments as posts and other things that would be better served by > good metadata. A refactoring of these is in order, and if a combined > meta helps push that along, I'm all for it.
A single meta table makes much more sense than adding a new comment_meta table if we want to have meta information for comments (which some people do) Moving options to a meta table is IMHO a bad idea - if you are going to do that you might as well just do away with the post table and comments tables as well and store all the data in one big table as key=>value pairs. A meta table should just be for meta - i.e. key<>vlaue pairs which are related to a particular object type be it a comment/post/...
Plugins should be using these tables where they want to store that type of information - unfortunately a lot of plugin authors do not understand the flexibility that already exists in the WP db schema and so for example every new event plugin creates a new table - the fact that the posts table has the ability there to store other types of post (and to be fair an event is very similar it what it wants to store) and the posts table could support events as-is.
As for attachments -> making them post meta sounds like a really good idea - doing away with the dodgy linked page for an attachment which can have very hard to moderate comments would be a good idea!
On 10/12/07, Peter Westwood <peter.westw...@ftwr.co.uk> wrote:
> Moving options to a meta table is IMHO a bad idea - if you are going > to do that you might as well just do away with the post table and > comments tables as well and store all the data in one big table as > key=>value pairs. > A meta table should just be for meta - i.e. key<>vlaue pairs which > are related to a particular object type be it a comment/post/...
Oh, I disagree there, for two main reasons: 1. The options table basically is a key/value table already. Look closely. 2. The "blog" is itself a valid "object" which can have associated metadata. To pick an example, what is the blog name other than metadata about the blog? How about what theme the blog is using? The options *are* metadata. _______________________________________________ wp-hackers mailing list wp-hack...@lists.automattic.com http://lists.automattic.com/mailman/listinfo/wp-hackers
Otto wrote: > We're doing weird stuff in WP too, like > attachments as posts and other things that would be better served by > good metadata. A refactoring of these is in order, and if a combined > meta helps push that along, I'm all for it.
I would be strongly against moving attachments or other types of sub-posts out of the posts table. If nothing else, these items need the ability to have their own comments.
> For WordPress 2.4, I propose that we add a general meta-data table. > It would be similar in structure to the current postmeta and usermeta > tables, except with an additional field specifying the domain of the > meta-data.
> There are at least a couple of benefits to such a table:
> - We could have meta-data for comments and categories. > Others have proposed meta tables for the comments [1]; rather than > adding meta tables seriatim, a general meta-data table would meet that > need and offer the possibility of actually reducing the total number > of tables.
> - It would provide a better place for plugins to store data. > Currently, most plugins---when they don't have enough data to justify > creating their own table---store data in the options table. I think > there are disadvantages to bloating the options table, one of which is > that WP loads almost all options into the object cache.
