Textpattern CMS support forum
You are not logged in. Register | Login | Help
- Topics: Active | Unanswered
Move custom fields to their own tables
Continuing from here.
This is the schema I have so far – I haven’t worked on the if_cf tags yet. My goal with the if_cf tags is to keep the functionality the same, so this could end up in 4.0.x. I realize this is a fair amount of work, but I’m hoping to work on it more today and during this week.
I guess something similar would work for categories (or maybe even make cfs generic enough to replace them), but I’d rather implement custom fields first.
-- stores cf names (custom_1, custom_2, etc.)
create table txp_cfs(
ID int auto_increment,
type int not null,
name varchar(20) not null,
primary key(ID)
);
-- stores cf type (input, textarea, etc.)
create table txp_cf_types(
ID int auto_increment,
name varchar(15) not null,
primary key(ID)
);
-- stores cf values
create table txp_cf_values(
ID int auto_increment,
parentID int not null,
value varchar(255),
primary key(id, parentID)
);Edit: Whoops – copy-paste error.
Last edited by jm (2009-03-12 05:39:52)
Offline
Re: Move custom fields to their own tables
jmd> do you take amphetamines? That’s an awfully big job.
Offline
Re: Move custom fields to their own tables
Despite living in MT, no I don’t. It is, however, spring break and -2ºF outside, so if I get my theses done, I should have some extra time :).
Offline
Re: Move custom fields to their own tables
jm wrote:
This is the schema I have so far
Cursory glance: looks good. Though I think your primary keys are switched on txp_cfs and txp_cf_values (unless you intend to make cf’s nestable and are going to add a parentID to the txp_cfs table??)
A handful of points off the top of my head, in no particular order:
- Keeping values separate from names is good because it opens up the possibility of more than one value per custom field, although that would have to be mediated by the type, which makes things very interesting from a UI perspective (having said that, Gerhard managed it, so it’s doable)
- Isn’t an implication of (1) that the if_cf tags would need to be phased in, so they at first assume there is only one ‘type’ (text)? Thus the conditionals check an exact match as they do now. But further down the line in the case of checkboxes, say, you’d have to offer if_cf is blah and/or blah and/or blah. Or perhaps if_cf is checked/unchecked/selected, which gets a bit hairy in terms of syntax
- A further implication is referential integrity between name/value and maintaining type integrity. If you delete a CF name what happens to its data which is assigned to the parentID? Deleted? Saved for a rainy day/bloat? User’s choice? How is that implemented? (at the moment, the data is left behind because it is intrinsic to the article, correct?)
- One nice thing about your ambiguous ‘parentID’ is that cfs can then be extended to other data types if it makes sense in future… (custom fields for files anyone?) but it would require an additional column, probably in both txp_cfs and txp_cf_values so you could differentiate which tab a particular field appears on. Should this be present from day 1 but forced to be ‘article’ for now, until the rest of the interface can catch up?
- Taking (4) a stage further, could custom fields per section/category be a reality? Either as a plugin like sed_sf or in the core, via a linking table that joined an article section (or an image/file category) with a set of CF names/IDs? Thinking out loud here, which is probably stupid.
- I assume TXP will ship with 2 ‘new’ custom field names defined: custom_1 and custom_2, both of type text?
- Any and all queries that use custom_1 etc will need rewriting throughout the core, and in plugins. Is it possible to rewrite queries on the fly to the new structure during transition or is it going to have to be a planned announcement and a cutoff date from which all plugins that have not adopted the new scheme will cease to function?
I know one thing that kept biting me when implementing smd_tags (which is essentially unlimited categories for all content types) was maintaining referential integrity when deleting (nested) tags and I had to put in options to either delete the whole subtree or promote any orphans up a level. Although the nesting isn’t an issue here, the same sort of thing will probably apply when deleting cf names, unless the decision is taken to ‘not care’ about orphaned data. In which case, what happens if you stop using a select list custom field and then sometime later assign the same name (perhaps unwittingly) to a text field? Do you get a slew of data automatically assigned from your relic custom field?
Either way, I echo mrdale’s sentiments. It’s a truckload of work to maintain some semblance of backwards compatibility or offer a migration path. But man, if you can pull it off you’ll be a hero! If you wanna bounce some ideas around, I’ll do my best to make my old skool brain keep up.
Last edited by Bloke (2009-03-10 22:02:40)
The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.
Txp Builders – finely-crafted code, design and Txp
Offline
Re: Move custom fields to their own tables
Let make custom_field of every content type (articles, files, images, links etc?) the strongest part of textpattern! :) The second slogan would be – glz_custom_fields into the core!
Sorry for a little offtopic, couldn’t take myself
P.S. Wouldn’t dividing custom_fields into three tables (name, type, content) complicate sql-queries so, that nobody will be able to write a plugin for working with them? Why not to store all this in one table, like it’s done for other elements of textpattern now?
Providing help in hacking ATM! Come to courses and don’t forget to bring us notebook and hammer! What for notebook? What a kind of hacker you are without notebok?
Offline
Re: Move custom fields to their own tables
the_ghost wrote:
Wouldn’t dividing custom_fields into three tables (name, type, content) complicate sql-queries so, that nobody will be able to write a plugin for working with them? Why not to store all this in one table, like it’s done for other elements of textpattern now?
Yes, that’s kind of what I was getting at with the ‘referential integrity’ thing. Keeping stuff together has advantages when trying to get data out easily, but it’s not the most normalised structure so you get data redundancy when storing stuff.
If the normalised version is the way forward, one possible avenue for exploration — and this has applications elsewhere — is to use txplib_db to hold API functions for dealing with custom fields in an atomic manner. e.g. safe_cf_update(), safe_cf_insert() etc.
Last edited by Bloke (2009-03-10 23:24:50)
The smd plugin menagerie — for when you need one more gribble of power from Textpattern. Bleeding-edge code available on GitHub.
Txp Builders – finely-crafted code, design and Txp
Offline
Re: Move custom fields to their own tables
♫ It’s beginning to look a lot like xPattern ;)
Last edited by mrdale (2009-03-10 23:36:29)
Offline
Re: Move custom fields to their own tables
Bloke wrote:
Though I think your primary keys are switched on txp_cfs and txp_cf_values (unless you intend to make cf’s nestable and are going to add a parentID to the txp_cfs table??)
D’oh! Copy-pasting is hard! By parentID, I meant the article ID. articleID would be clearer.
Isn’t an implication of (1) that the if_cf tags would need to be phased in, so they at first assume there is only one ‘type’ (text)?
Yes – that’s what I was doing in my migration script (not really done). What I was thinking is that the type column would primarily be used by the admin screen (or plugins), but the actual front-end code would just output the value (e.g., <txp:if_custom_field name="myRadioButton">...).
A further implication is referential integrity between name/value and maintaining type integrity. If you delete a CF name what happens to its data which is assigned to the parentID? Deleted? Saved for a rainy day/bloat? User’s choice? How is that implemented? (at the moment, the data is left behind because it is intrinsic to the article, correct?)
I wish MyISAM had foreign key support, but unless we move to InnoDB, I guess we’ll have to do it with PHP. In light of this, I think it should be “faked” – if an article is deleted, delete the corresponding cf values.
Should this be present from day 1 but forced to be ‘article’ for now, until the rest of the interface can catch up?
I think forcing cfs to be linked to an articleID is a good approach – it’ll certainly get done faster. Related to #5, I’m not keen on making things so generic because it’ll introduce a lot of complexity.
I assume TXP will ship with 2 ‘new’ custom field names defined: custom_1 and custom_2, both of type text?
I think so – basically retain backward-compatibility in terms of tags and the admin UI.
Any and all queries that use custom_1 etc will need rewriting throughout the core, and in plugins. Is it possible to rewrite queries on the fly to the new structure during transition or is it going to have to be a planned announcement and a cutoff date from which all plugins that have not adopted the new scheme will cease to function?
Rewriting queries would be a pain, so I think a cutoff date would probably be best. Crap – this would probably make it crockery. I guess this would be the biggest issue. Any idea how many plugins access the custom_n values from the textpattern db? Fixing abandoned ones wouldn’t be too bad, but it’s probably more outside the scope of an average user’s capabilities. (Related – shouldn’t .org have a “works with version…” field?)
the_ghost wrote:
P.S. Wouldn’t dividing custom_fields into three tables (name, type, content) complicate sql-queries so, that nobody will be able to write a plugin for working with them? Why not to store all this in one table, like it’s done for other elements of textpattern now?
Joins will not make plugin writing impossible. Storing everything in one table brings us to the current situation – there’s a limit on the number of custom fields and the types of those fields.
mrdale wrote:
♫ It’s beginning to look a lot like xPattern ;)
I think if it’s kept specific enough (cfs are only for articles), it has a chance.
Last edited by jm (2009-03-12 06:05:51)
Offline
Re: Move custom fields to their own tables
jm wrote:
I guess this would be the biggest issue.
This is what I meant with …(No, database views are not an option.)….
Any idea how many plugins access the custom_n values from the textpattern db?
IMHO, this is not a question of “how many”, but “which ones”. I suspect that those are just the ones which provide modded cf functionality right now, so you’d have a solid chance that your target audience is either forced to migrate the hard way or locked into their current solution.
Offline
Re: Move custom fields to their own tables
Wierd… what happened to hak’s post?
Offline
Re: Move custom fields to their own tables
I got rid of it. It wasn’t adding anything to the discussion that had not been said before.
I figured I’d refrain from diverting yet another thread into content types territory.
Shoving is the answer – pusher robot
Offline